CODEWITHSUNDEEP
mysqlsqlsql-update
Ben
Ben

Reputation: 1011

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.

Upvotes: 15

Views: 58219

Answers (4)

peterm
peterm

Reputation: 92805

EDIT For MySql it'll be

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

sqlfiddle

Original answer was for SQL Server

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

sqlfiddle

Upvotes: 4

John Woo
John Woo

Reputation: 263803

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

Upvotes: 27

jmontross
jmontross

Reputation: 3581

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

Upvotes: 0

Linga
Linga

Reputation: 10563

You can try this:

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

Upvotes: 3

Related Questions