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.

1
2
3
4
5
6
7
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.

1
2
3
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