how to update column from multiple table in mysql

Question

Hi guys, I want to update columns from two tables.

For example: abc and xyz. The abc table contains fields called id, fname, lastname, status. And the table xyz contains id, x_id and status. Now what I want is to update a column status from abc and status column from xyz by using single query.

I try this query, but it is not working.

UPDATE abc a1 
JOIN xyz x1 ON a1.id = x1.x_id 
SET a1.status = "D" , 
    x1.status = "delete" 
WHERE a1.id = 15 AND x1.x_id = 15;

Thanks.

Check: http://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query and http://stackoverflow.com/questions/1871537/how-to-update-two-tables-in-one-statement — Anda Iancu, Feb 15 '13 at 07:56

Bojan Kovacevic · Answer 1 · 2013-02-15T11:36:58.027

0

Edited for 3 tables:

UPDATE a1,x1,s1 
SET a1.status = "D" , 
x1.status = "delte",
s1.status = "D" 
WHERE a1.id = x1.x_id
AND a1.id = s1.s_id 
AND a1.id = 15;

edited Feb 15 '13 at 11:36

answered Feb 15 '13 at 07:59

Bojan Kovacevic

778
7
19

hey Bojan can we do this with 3 tables? – Akki Feb 15 '13 at 10:23
should work with same syntax (ofc with all connections between tables in WHERE statement – Bojan Kovacevic Feb 15 '13 at 10:34
suppose i added new table called srk with fields id, s_id and status then how i find common field in WHERE CLAUSE – Akki Feb 15 '13 at 10:37
UPDATE a1,x1,S1 SET a1.status = "D" , x1.status = "delte" ,S.status = 'D' WHERE a1.id = x1.x_id AND a1.id = 15 AND x1.x_id = 15; – Akki Feb 15 '13 at 10:38
what is s_id? foreign key to what table? – Bojan Kovacevic Feb 15 '13 at 10:39
i write like where a1.id = x1.id and a1.id = s1.id but its not working. – Akki Feb 15 '13 at 10:41
then you need to have WHERE a1.id = s1.s_id (primary to foreign key) – Bojan Kovacevic Feb 15 '13 at 10:44
please try to learn database concepts and connection between tables. – Bojan Kovacevic Feb 15 '13 at 10:45
when connecting few tables you only need to connect primary key with foreigh key that is connected to it. It is easy when you learn concept. And for exact SQL command you need to know what values you wants to update and how are tables connected. See updated answer. – Bojan Kovacevic Feb 15 '13 at 11:39

score 0 · Answer 2 · answered Feb 15 '13 at 08:03

If your mysql server cannot update two tables in a single query using SQL. You can use LOCK TABLES command to avoid race condition :

LOCK TABLES abc WRITE, xyz WRITE;
update abc set status = "D"  where id = 15;
update xyz set status = "delete"  where x_id = 15;
UNLOCK TABLES;

Regards, Omar

how to update column from multiple table in mysql

2 Answers2