hinteractive02 hinteractive02 - 8 days ago 6
SQL Question

SQL - Update Multiple Tables, Where Inner Join Value is from Third Table

In a MySQL Database I have tables labeled as the following:

Table:catalog_product_entity
|-------------------------|
|entity_id | sku |
|-------------------------|
|5 | 094922562333 |
|6 | 087454664234 |
|7 | 054545789548 |
---------------------------

Table:catalog_product_entity_decimal
|-------------------------|
|entity_id | price |
|-------------------------|
|5 | 39.99 |
|6 | 37.92 |
|7 | 5.99 |
---------------------------

Table: cataloginventory_stock_item
|-------------------------|
|entity_id | qty |
|-------------------------|
|5 | 0 |
|6 | 5 |
|7 | 8 |
---------------------------


I'm connecting to another database that can only provide me the SKU. I'm wanting to use the SKU as a INNER JOIN to update the tables using the entity_id with one query.

Here is the query I have so far (getting syntax error):

UPDATE catalog_product_entity_decimal, cataloginventory_stock_item
SET catalog_product_entity_decimal.value ='37.95',
cataloginventory_stock_item.qty ='4'
INNER JOIN catalog_product_entity
ON catalog_product_entity.entity_id = catalog_product_entity_decimal.entity_id
WHERE catalog_product_entity.sku = '094922562333';

Answer

The JOIN is part of the UPDATE statement, not after the SET. So you might try:

UPDATE catalog_product_entity_decimal cped JOIN
       catalog_product_entity cpe
       ON cpe.entity_id = cped.entity_id JOIN
       cataloginventory_stock_item csi
       ON csi.entity_id = cpe.entity_id
    SET cped.value = 37.95,                                                          
        csi.qty = 4
    WHERE cpe.sku = '094922562333';

Notes:

  • The JOINs are in the UPDATE clause.
  • Table aliases make the query easier to write and to read.
  • You need a JOIN for cataloginventory_stock_item.
  • Single quotes are not required around numbers.