Support `JOIN`s in `UpdateStatement` (`join_subquery`, `inner_join`, etc.)

[LeoniePhiline]

Motivation

It is rather handy (and often required?) to gather related entities in an UPDATE statement, and update some fields based on joined tables.

For example, here each institution has several subscriptions (with subscriptions.institution being a foreign key to institutions.uid).

An append-only activity table records users of these institutions using the subscriptions.

Periodically, the institutions' and subscriptions' last_activity datetime field shall be updated.

Updating last_activity on subscriptions

UPDATE subscriptions AS entity
INNER JOIN (
    SELECT subscription.uid AS entity_uid, MAX(date_time) AS last_activity
    FROM activity
    INNER JOIN subscriptions AS  subscription ON subscription.uid = activity.subscription
    GROUP BY subscription.uid
) AS entity_activity
    ON entity_activity.entity_uid = entity.uid
SET entity.last_activity = entity_activity.last_activity;

Updating last_activity on institutions

UPDATE institutions AS entity
INNER JOIN (
    SELECT institution.uid AS entity_uid, MAX(date_time) AS last_activity
    FROM activity
    INNER JOIN subscriptions AS subscription ON subscription.uid = activity.subscription
    INNER JOIN institutions AS institution ON institution.uid = subscription.institution
    GROUP BY institution.uid
) AS entity_activity
    ON entity_activity.entity_uid = entity.uid
SET entity.last_activity = entity_activity.last_activity;

My wish here was to use (simplified):

let query = Query::update()
    .table(Subscriptions::Table)
    .join_subquery(
        JoinType::InnerJoin,
        ...
    )
    .value(...);

But joins are unavailable on UpdateStatement.

Proposed Solutions

Add methods available on SelectStatement to UpdateStatement:

• join
• join_as
• join_lateral
• join_subquery
• inner_join
• left_join
• right_join
• full_outer_join

Additional Information

I will try to work around this using CTEs (UpdateStatement::with). Edit: This workaround won't work in MariaDB: https://jira.mariadb.org/browse/MDEV-18511

This feature request for UPDATE TABLE ... JOIN ... SET ... is related to #627 - a feature request for DELETE ... FROM ... JOIN ....

[tyt2y3]

SeaQuery has support for UPDATE SELECT FROM and UPDATE SET=SUB_QUERY, so they might also do the same trick.

[LeoniePhiline]

I'm afraid these aren't supported by MariaDB. But I might be missing something...

Update Syntax is documented here: https://mariadb.com/kb/en/update/

And table_references as mentioned at the above URL are documented here: https://mariadb.com/kb/en/join-syntax/

Is table_factor table_subquery (see documentation at second URL) the syntax you are referring to?

If UPDATE (SELECT FROM) was supported by MariaDB, how would I construct this query with an UpdateStatement?

[tyt2y3]

Sad, we only got INSERT SELECT FROM but not in update. Ref: https://docs.rs/sea-query/latest/sea_query/query/struct.InsertStatement.html#method.select_from

I think it can be added.

[LeoniePhiline]

That would be greatly appreciated! =)

[AdamJSoftware]

Any news on this?