Update a Select Query

Question

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]+')

Let's see . . . You have a subquery for in that returns a zillion columns and you are wondering where the error is? — Gordon Linoff, Jan 24 '17 at 17:01

Rajat Mishra · Accepted Answer · 2017-01-24T17:11:05.623

1

    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]+')

edited Jan 24 '17 at 17:11

answered Jan 24 '17 at 17:09

Rajat Mishra

3,635
4
27
41

What is NVL? and what does it do? – Astrea Jan 24 '17 at 17:10
@Astrea sorry my bad. NVL is function in oracle for handling null values. Similar to coalesce. have updated the answer – Rajat Mishra Jan 24 '17 at 17:13
Thank You so much for your help. This is causing a new error #1093 - You can't specify target table 'oc_product_option_value_dimension' for update in FROM clause. Which from this question http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause . i found out that it is because i'm using oc_product_option_value in my Select Statement – Astrea Jan 24 '17 at 17:26
@Astrea so basically first error was due to the `is null in` clause. – Rajat Mishra Jan 24 '17 at 17:34

Update a Select Query

1 Answers1