Update from select with multiple fields?

Question

I can do the following to insert records into a table from a select on another table:

INSERT INTO table (field1, field2) SELECT field1,field2 FROM table2

Can I do the same with an update ?? Something like this (not working!):

UPDATE table SET field1=table2.field1, field2=table2.field2 SELECT field1,field2 FROM table2
WHERE table.field0=table2.field0

I know how to do this with only 1 field, but is there a way to do it with multiple fields?

Possible Duplicate... http://stackoverflow.com/questions/2334712/sql-server-update-from-select — RiddlerDev, Jul 22 '11 at 21:02

score 11 · Accepted Answer · answered Jul 22 '11 at 21:20

11

UPDATE table A INNER JOIN table2 B USING (field0)
SET A.field1 = B.field1,A.field2 = B.field2;

answered Jul 22 '11 at 21:20

RolandoMySQLDBA

43,883
16
91
132

where did `field0` come from? – tumultous_rooster Oct 30 '15 at 01:14

score -1 · Answer 2 · answered Jul 22 '11 at 21:18

-1

Yes, like this:

UPDATE table SET 
field1=(select field1 from table2 WHERE field0=table1.field0)
field2=(select field2 from table2 WHERE field0=table1.field0)
-- WHERE some condition (optional)

answered Jul 22 '11 at 21:18

Bohemian

412,405
93
575
722

Doesn't this run the same query twice, but only return different attributes each time – ErJab Nov 09 '11 at 04:31

Update from select with multiple fields?

2 Answers2