Ben Ben - 7 months ago 39
SQL Question

MySQL UPDATE syntax with multiple tables using WHERE clause

Case:



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

Problem:



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.

Answer

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