Set value to NULL from select query in SQL-CodePudding

I have a select query to get data like this:

SELECT cpe.entity_id,
       cpe.type_id,
       cpe.sku,
       cped.value AS "PRICE"

FROM catalog_product_entity AS cpe

LEFT JOIN catalog_product_entity_decimal AS cped 
ON cped.entity_id = cpe.entity_id

WHERE cpe.type_id = 'configurable' AND cped.attribute_id = 77

Now I want to update my cped.value column to null for all row, I tried update query like this:

UPDATE 
    cped
SET
    cped.value = NULL
FROM 
    catalog_product_entity AS cpe
    LEFT JOIN catalog_product_entity_decimal AS cped 
    ON cped.entity_id = cpe.entity_id
WHERE 
    cpe.type_id = 'configurable' 
    AND cped.attribute_id = 77

But it got Syntax error near 'FROM catalog_product_entity AS cpe LEFT JOIN catalog_product_entit...' at line 5.

How I can fix this?

Thank you very much!

CodePudding user response：

UPDATE has no FROM clause, so you need to join the tables in the UPDATE clause

UPDATE catalog_product_entity_decimal cped
RIGHT JOIN catalog_product_entity AS cpe  ON cped.entity_id = cpe.entity_id
SET
    cped.value = NULL

WHERE 
    cpe.type_id = 'configurable' 
    AND cped.attribute_id = 77;