Set value to NULL from select query in SQL

Question

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!

Your LEFT JOIN returns regular INNER JOIN result. If you want true LEFT JOIN result, move the cped.attribute_id condition from WHERE to ON. — jarlh
– jarlh, Commented Mar 27, 2022 at 19:29

nbk · Accepted Answer · 2022-03-27 19:34:46Z

1

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;

answered Mar 27, 2022 at 19:34

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Set value to NULL from select query in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related