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

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

Comments

comments