INSERT data from one table to another where ids match

Question

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?

Answer 1

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`;