CODEWITHSUNDEEP
mysql
LTech
LTech

Reputation: 1761

INSERT data from one table to another where ids match

I have two tables:

1. `blog_export`: 'id', 'body'. 
'id' already has values 'body' is empty. 

2. `field_data_body`: 'body_value','entity_id'

I would like to copy body_value from field_data_body and insert that data into the column 'body' on the table blog_export but ONLY where 'id' matches 'entity_id'

I have the statement 

INSERT INTO `blog_export` (`body`)
SELECT `body_value`
FROM `field_data_body`
WHERE `bundle` = 'wp_blog' AND `entity_id` = `blog_export`.`id`

but it doesn't work. How do I do this?

Upvotes: 3

Views: 6623

Answers (2)

lp_
lp_

Reputation: 1178

Depending on your current situation with your tables, you have at least these two options.

1) blog_export already has records for all the records with possible entity_id values in field_data_body. In this case, you just need to update all the records and you can simply use an update statement (instead of your insert), as others already recommended.

Either use the multiple-table syntax (just as in Rahul's answer):

UPDATE blog_export be
JOIN field_data_body fdb ON fdb.entity_id = be.id
SET be.body = fdb.body_value;

Or the single-table syntax:

UPDATE blog_export be
SET body = (SELECT body FROM field_data_body WHERE entity_id = be.id);

2) blog_export does not contain all the records with possible entity_id values in field_data_body (i.e. none of them, or maybe just a subset of them). In this case, you must insert new records to the table and update already existing ones.

You can use an insert statement with the ON DUPLICATE KEY UPDATE clause (supposing that blog_export.id is unique or primary key):

INSERT INTO blog_export (id, body)
SELECT entity_id, body_value
FROM field_data_body
ON DUPLICATE KEY UPDATE body=body_value;

Upvotes: 2

Rahul
Rahul

Reputation: 77866

You need to perform an UPDATE operation instead joining with other table like

UPDATE `blog_export` be
JOIN `field_data_body` fdb ON fdb.`entity_id` = be.`id`
SET be.`body` = fdb.`body_value`;

Upvotes: 7

Related Questions