Update Two Tables with Joins in MySQL
Here we show an example of updating two table at the same time using joins. Suppose we have two tables:
lookup which holds information on a person, and
activity which holds certain activities happening on a person. Imagine that
lookup is stable, while
activity is updated frequently, and constantly we need to synchronize the information in two tables.
The following SQL set up the tables.
CREATE TABLE lookup (id INT PRIMARY KEY, name VARCHAR(64), last_use TIMESTAMP DEFAULT CURRENT_TIMESTAMP); INSERT INTO lookup (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carl'), (4, 'Eva'); CREATE TABLE activity (id INT, name VARCHAR(64), info TEXT); INSERT INTO activity (id, info) VALUES (1, 'Alice Info'), (2, 'Bob Info'), (3, 'Carl Info');
Now we would like to update
activity by filling up the names; simultaneously, we wish to update
lookup by setting the column
last_use to the current timestamp.
The solution is given below. There are two tables in this UPDATE statement; the two tables are joined on the column
UPDATE lookup, activity SET lookup.last_use = CURRENT_TIMESTAMP, activity.name = lookup.name WHERE activity.id = lookup.id;
The result of this statement shows 6 rows were updated, 3 from
lookup and 3 from