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
.