CODEWITHSUNDEEP
phpmysqlsqlmysql-error-1064
wizz
wizz

Reputation: 3

MySQL server version for the right syntax to use near ''

I have Mysql query problem. My query is 

UPDATE oc_product p 
INNER JOIN oc_currency as c 
ON p.price2_currency_code=c.`code` 
SET p.price=CASE WHEN p.price2_currency_code='EUR' THEN p.price2*c.`value` 
WHEN p.price2_currency_code='USD' THEN p.price2*c.`value` 
ELSE p.price;

I am getting always

[Err] 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 '' at line 4

Upvotes: 0

Views: 3320

Answers (5)

Arpita
Arpita

Reputation: 1398

Add an END CASE in end of your query and you are all set.

UPDATE oc_product p 
INNER JOIN oc_currency as c 
ON p.price2_currency_code=c.`code` 
SET p.price=CASE WHEN p.price2_currency_code='EUR' THEN p.price2*c.`value` 
WHEN p.price2_currency_code='USD' THEN p.price2*c.`value` 
ELSE p.price
END CASE;

Upvotes: 0

mynawaz
mynawaz

Reputation: 1594

You have only two conditions so you can also use an IF which is simpler than the CASE

UPDATE oc_product p 
    INNER JOIN oc_currency as c 
    ON p.price2_currency_code=c.`code` 
SET p.price=
    IF(p.price2_currency_code='EUR' OR p.price2_currency_code='USD', p.price2*c.`value`, p.price);

Upvotes: 0

Abdulla Nilam
Abdulla Nilam

Reputation: 38609

$sql = "
    UPDATE oc_product AS p
    INNER JOIN oc_currency as c
    ON p.price2_currency_code=c.`code`
    SET p.price=case WHEN p.price2_currency_code='EUR' THEN p.price2*c.`value`
    WHEN p.price2_currency_code='USD' THEN p.price2*c.`value`
    ELSE p.price
    END";

Upvotes: 0

Harshit
Harshit

Reputation: 5157

May be you missed the END

UPDATE oc_product p 
INNER JOIN oc_currency as c 
ON p.price2_currency_code=c.`code` 
SET p.price=CASE WHEN p.price2_currency_code='EUR' THEN p.price2*c.`value` 
WHEN p.price2_currency_code='USD' THEN p.price2*c.`value` 
ELSE p.price
END

Upvotes: 3

Ullas
Ullas

Reputation: 11556

Try with the following query:

update p
set p.price = (case when p.price2_currency_code='EUR' 
then p.price2*c.`value` 
when p.price2_currency_code='USD' then p.price2*c.`value` 
else p.price end)
from oc_product p
join oc_currency c
on p.price2_currency_code=c.`code`;

Upvotes: 0

Related Questions