MySQL UPDATE syntax with multiple tables using WHERE clause

Question

Case:

How to update table1 with data from table2 where id is equal?

When I run the following update statement, it updates all the records in table1 (even where the id field in table1 does not exist in table2).

How can I use the the multiple update table syntax, to update ONLY the records in table1 ONLY where the id is present in table2 and equal?

UPDATE table1,table2
SET table1.value=table2.value 
WHERE table2.id=table1.id

Thanks in advance.

John Woo · Accepted Answer · 2013-02-23T06:53:38.650

27

here's the correct syntax of UPDATE with join in MySQL

UPDATE  table1 a
        INNER JOIN table2 b
            ON a.ID = b.ID
SET     a.value = b.value

edited Feb 23 '13 at 06:53

answered Feb 23 '13 at 06:45

John Woo

I can see it is a bit different thanthan what @peterm wrote... how is this the correct one? Thank You in advance, love to learn this stuff – Ben Feb 23 '13 at 06:47
the syntax of @peterm is also correct but it works on `SQL Server` not on `MySQL`. – John Woo Feb 23 '13 at 06:50
This is why I love stack...perfect answer and I did not have to formulate a question already answered here and get flamed for it LOL...great stuff, thanks! – Rocco The Taco Mar 19 '15 at 12:14

peterm · Answer 2 · 2013-02-23T07:00:04.707

4

EDIT For MySql it'll be

UPDATE table1 t1 INNER JOIN 
       table2 t2 ON t2.id = t1.id
   SET t1.value = t2.value

Original answer was for SQL Server

UPDATE table1
   SET table1.value = table2.value 
  FROM table1 INNER JOIN 
       table2 ON table2.id=table1.id

edited Feb 23 '13 at 07:00

answered Feb 23 '13 at 06:40

peterm

What about using a sub-select? Which is better? – Ben Feb 23 '13 at 06:41
@BenJones IMHO `JOIN` is the way to go. Sorry didn't pay attention that it was needed for mysql. My original answer was for SQL Server. – peterm Feb 23 '13 at 07:01

score 3 · Answer 3 · answered Feb 23 '13 at 06:41

3

You can try this:

UPDATE TABLE1
SET column_name = TABLE2.column_name
FROM TABLE1, TABLE2
WHERE TABLE1.id = TABLE2.id

answered Feb 23 '13 at 06:41

Linga

I don't think that will address the issue I am having of updating all the records in table1? – Ben Feb 23 '13 at 06:42
If so, you mention all the column names – Linga Feb 23 '13 at 06:42

score 0 · Answer 4 · edited Feb 23 '13 at 06:50

0

UPDATE table1
SET table1.value = (select table2.value 
WHERE table2.id=table1.id)

edited Feb 23 '13 at 06:50

Ed Heal

answered Feb 23 '13 at 06:48

jmontross