MySQL/SQL query with errors

Question

I have this query below, with quite a few errors in.

Does anyone know MySQL/SQL good enough to fix the errors?

UPDATE shopproducts AS wesp
JOIN currencies AS wec
JOIN shopcategories AS wesc
JOIN shops AS wes
IF(wesc.adelivery = 1)
    SET wesp.totalprice = ROUND(wesp.price / 100 * wec.value, 0) + wes.adelivery
ELSE IF(wesc.bdelivery = 1)
    SET wesp.totalprice = ROUND(wesp.price / 100 * wec.value, 0) + wes.bdelivery
ELSE
    IF(wesp.price <= wes.freedelivery)
        SET wesp.totalprice = ROUND(wesp.price / 100 * wec.value, 0)
    ELSE
        SET wesp.totalprice = ROUND(wesp.price / 100 * wec.value, 0) + wes.stddelivery
WHERE wesp.currency = wec.name
AND wesp.sortcategory = wesc.category
AND wesp.shop = wes.name

Thanks!

what is the error you are getting – koushik veldanda Jan 13 '15 at 07:16 — koushik veldanda, Jan 13 '15 at 07:16
And what did **you** do to fix errors? – u_mulder Jan 13 '15 at 07:16 — u_mulder, Jan 13 '15 at 07:16

score 1 · Accepted Answer · answered Jan 13 '15 at 07:17

1

Use this way,

UPDATE shopproducts AS wesp
JOIN currencies AS wec
JOIN shopcategories AS wesc
JOIN shops AS wes
SET wesp.totalprice = case when (wesc.adelivery = 1) then ROUND(wesp.price / 100 * wec.value, 0) + wes.adelivery .....(like this way all the when) end  

WHERE wesp.currency = wec.name
AND wesp.sortcategory = wesc.category
AND wesp.shop = wes.name

answered Jan 13 '15 at 07:17

Charvee Shah

730
1
6
21

This also helped: http://stackoverflow.com/questions/12754470/mysql-update-case-when-then-else – Louisa Jan 13 '15 at 19:37

MySQL/SQL query with errors

1 Answers1