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 ....