开发者

MYSQL - Move data from one table to a related one?

开发者 https://www.devze.com 2023-01-04 21:11 出处:网络
I have two tables, structured like so: table A: A_IDvarchar(32) PRIMARY KEY field1varchar(255) field2varchar(255)

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消