[FIXED] Update a Select Query

Issue

I am trying to get rid of all the NULL values and replace them with an = sign in the column dimension_prefix from the results of the query. I keep getting a SYNTAX error though, I can't figure out where I'm wrong. Any help Is much appreciated. Thanks

I am getting the

Error #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 'IN( >SELECT dimension_prefix,length,width,height,ovd.name,p.`product_i' at >line 1

UPDATE oc_product_option_value_dimension SET dimension_prefix = '='     WHERE dimension_prefix IS NULL IN(
SELECT `dimension_prefix`,`length`,`width`,`height`,ovd.`name`,p.`product_id`,pov.product_option_value_id

FROM ( SELECT product_id FROM `oc_product` ORDER BY product_id ASC) AS p
LEFT JOIN `oc_product_option_value` pov ON     pov.product_id=p.product_id 
LEFT JOIN `oc_product_option_value_dimension` povd ON povd.product_option_value_id=pov.product_option_value_id
LEFT JOIN `oc_option_value_description` ovd ON ovd.option_value_id=pov.option_value_id
LEFT JOIN `oc_option_description` od ON od.option_id=ovd.option_id
WHERE ovd.`name` regexp '^[0-9]+')

Solution

    Actually you are trying is null in .. which is giving you the syntax error.

UPDATE oc_product_option_value_dimension SET dimension_prefix = '=' 
    WHERE coalesce(dimension_prefix,"@@") = (
        SELECT coalesce(`dimension_prefix`,"@@")
        FROM ( SELECT product_id FROM `oc_product` ORDER BY product_id ASC) AS p
        LEFT JOIN `oc_product_option_value` pov ON     pov.product_id=p.product_id 
        LEFT JOIN `oc_product_option_value_dimension` povd ON povd.product_option_value_id=pov.product_option_value_id
        LEFT JOIN `oc_option_value_description` ovd ON ovd.option_value_id=pov.option_value_id
        LEFT JOIN `oc_option_description` od ON od.option_id=ovd.option_id
        WHERE ovd.`name` regexp '^[0-9]+')

Answered By - Rajat Mishra