I have two tables, structured like so:
table A:
A_ID varchar(32) PRIMARY KEY
field1 varchar(255)
field2 varchar(255)
开发者_运维问答B_ID varchar(32)
table B:
B_ID varchar(32) PRIMARY KEY
field1 varchar(255)
field2 varchar(255)
A contains a foreign key to table B (note that 1 B could have more than 1 A). I'd like to insert the data from table B to it's matching table A (field1 and field2 is empty for every row in table A currently). Is there a way to do this purely with MySQL?
Try
UPDATE `table_a` AS a
INNER JOIN `table_b` AS b ON ( a.`b_id` = b.`id` )
SET a.`field1` = b.`field1`, a.`field2` = b.`field2`
How about something like this:
UPDATE `table A`, `table B` SET `table A`.field1 = `table B`.field1, `table A`.field2 = `table B`.field2 WHERE `table B`.ID = `table A`.B_ID
EDIT: Nevermind, I was beaten to it.
精彩评论