CODEWITHSUNDEEP
mysql
GSto
GSto

Reputation: 42380

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

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?

Upvotes: 3

Views: 225

Answers (2)

Kerry Jones
Kerry Jones

Reputation: 21858

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`

Upvotes: 3

user269597
user269597

Reputation:

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.

Upvotes: 2

Related Questions