SQL Left Join Insert not working

Question

I am wanting to get the entity_id value from each row in the catalog_product_entity table, and each store_id value from each row in the catalog_product_entity_int table, where the entity_id of the first table is equal to the entity_id of the second table.

What is wrong with this SQL insert statement:

INSERT INTO `catalog_product_entity_varchar` ("","4","231",i.store_id,p.entity_id,"D") SELECT p.entity_id, i.store_id FROM catalog_product_entity p LEFT JOIN catalog_product_entity_int i ON i.entity_id = p.entity_id;

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"","4","231",i.store_id,p.entity_id,"D") SELECT p.entity_id, i.store_id FR' at line 1

it should be like this . INSERT INTO catalog_product_entity_varchar SELECT '',4, 231, i.store_id, p.entity_id, 'D' FROM catalog_product_entity p LEFT JOIN catalog_product_entity_int i ON i.entity_id = p.entity_id; — radar
– radar, Commented Dec 8, 2014 at 18:57

crthompson · Accepted Answer · 2014-12-08 19:03:05Z

You have confused two types of insert statements.

If you are trying to select the values from one table to insert it would look like this:

INSERT INTO `catalog_product_entity_varchar` (field1, field2, field3, field4, field5, field6) SELECT "","4","231",i.store_id,p.entity_id,"D" FROM catalog_product_entity p LEFT JOIN catalog_product_entity_int i ON i.entity_id = p.entity_id;

Add the hardcoded portion of the values to the select statement and specify which fields they are going into in the insert.

Read up on insert select statements from the mysql doc page.

Tucker · Accepted Answer · 2014-12-08 18:58:34Z

you don't need to specify the fileds on an insert on select statment.

you're query should be something like

INSERT INTO `catalog_product_entity_varchar` SELECT p.entity_id, i.store_id --ADD MORE FIELDS HERE TO MATCH YOUR TARGET TABLE SCHEMA FROM catalog_product_entity p LEFT JOIN catalog_product_entity_int i ON i.entity_id = p.entity_id;

Notice that I dropped the ("","4","231",i.store_id,p.entity_id,"D")

You'll want to add all the fields in the SELECT statement that are required for an insert into your targettable

Collectives™ on Stack Overflow

SQL Left Join Insert not working

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related