UPDATE using JOIN

by jenny on 18 July 2008 - 11:58am in

update content_type_message ctm join node n on ctm.nid = n.nid set
  ctm.field_message_recipientalert_value = unix_timestamp(date_add(n.created, interval 24 hour)),
  ctm.field_message_senderalert_value = unix_timestamp(date_add(n.created, interval 24 hour));

From 12.2.10. UPDATE Syntax:

You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 12.2.7.1, “JOIN Syntax”. Here is an example:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The preceding example shows an inner join that uses the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

You need the UPDATE privilege only for columns referenced in a multiple-table UPDATE that are actually updated. You need only the SELECT privilege for any columns that are read but not modified.

...

Currently, you cannot update a table and select from the same table in a subquery.