Mysql update using select

Question

I'm trying to update the fields by using below query. What is the error in below query ?

MySQL said: Documentation

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 'FROM orders cl' at line 2

update hedging SET 
        Dept = concat(RIGHT( dpt.DeptName,2),LEFT( grp.GroupName,3),LEFT( st.Login,3)),
        OrderNo = cl.OrderNo,Client = cn.ShortName,
        Currency = cur.Notation, SellingAmount = pto.PIAmount , 
        BuyingAmount = pto.POAmount 
        FROM  orders cl
        left join client cn on cl.ClientID = cn.ClientID 
        inner join department dpt on cl.DeptID = dpt.DeptID
        inner join supplier sp on cl.SupplierID = sp.SupplierId 
        left join staff st on st.StaffID = cl.SalesPerson
        left join pipo_total pto on pto.OrderNo = cl.OrderNo
        inner join groups grp on cl.GroupID = grp.GroupID
        left join currency cur on cur.CurrencyID= cl.SellCurrencyID  
        left join hedging hed on hed.OrderNo = cl.OrderNo) 
        where cur.Notation <> 'USD' and cl.OrderType = '1' and hed.OrderNo = cl.OrderNo

Try 1

update orders cl 
        left join client cn on cl.ClientID = cn.ClientID 
        inner join department dpt on cl.DeptID = dpt.DeptID
        inner join supplier sp on cl.SupplierID = sp.SupplierId 
        left join staff st on st.StaffID = cl.SalesPerson
        left join pipo_total pto on pto.OrderNo = cl.OrderNo
        inner join groups grp on cl.GroupID = grp.GroupID
        left join currency cur on cur.CurrencyID= cl.SellCurrencyID  
        left join hedging hed on hed.OrderNo = cl.OrderNo
SET hed.Dept = concat(RIGHT( dpt.DeptName,2),LEFT( grp.GroupName,3),LEFT( st.Login,3))
    ,hed.OrderNo = cl.OrderNo
    ,hed.Client = cn.ShortName
    ,hed.Currency = cur.Notation
    ,hed.SellingAmount = pto.PIAmount
    ,hed.BuyingAmount = pto.POAmount
where cur.Notation <> 'USD' and cl.OrderType = '1' and hed.OrderNo = cl.OrderNo

Your syntax is wrong update table joins set where

M Khalid Junaid
– M Khalid Junaid

2015-06-03 06:29:03 +00:00
Commented Jun 3, 2015 at 6:29 — M Khalid Junaid
– M Khalid Junaid, Commented Jun 3, 2015 at 6:29
i used like you said same getting some error

Question User
– Question User

2015-06-03 06:31:37 +00:00
Commented Jun 3, 2015 at 6:31 — Question User
– Question User, Commented Jun 3, 2015 at 6:31

sqluser · Accepted Answer · 2015-06-03 06:45:20Z

2

UPDATE with multiple tables should be something like this

UPDATE  table1 t1
        INNER JOIN table2 t2 ON t1.ID = t2.ID
SET     t1.value = [value]

Edit: Your updated query

update  orders cl
        left join client cn on cl.ClientID = cn.ClientID 
        inner join department dpt on cl.DeptID = dpt.DeptID
        inner join supplier sp on cl.SupplierID = sp.SupplierId 
        left join staff st on st.StaffID = cl.SalesPerson
        left join pipo_total pto on pto.OrderNo = cl.OrderNo
        inner join groups grp on cl.GroupID = grp.GroupID
        left join currency cur on cur.CurrencyID= cl.SellCurrencyID  
        left join hedging hed on hed.OrderNo = cl.OrderNo) 
SET Dept = concat(RIGHT( dpt.DeptName,2),LEFT( grp.GroupName,3),LEFT( st.Login,3))
    ,OrderNo = cl.OrderNo
    ,Client = cn.ShortName
    ,Currency = cur.Notation
    ,SellingAmount = pto.PIAmount
    ,BuyingAmount = pto.POAmount
where cur.Notation <> 'USD' and cl.OrderType = '1' and hed.OrderNo = cl.OrderNo

edited Jun 3, 2015 at 6:45

answered Jun 3, 2015 at 6:31

sqluser

5,7027 gold badges44 silver badges52 bronze badges

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

5 Comments

Question User Over a year ago

thanks for ur query :) I need to update the hedging table and need to get some values from orders cl see my question and Try 1 query where im going wrong ?

sqluser Over a year ago

Did you try my query?

Question User Over a year ago

yes getting 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 'where cur.Notation <> 'USD' and cl.OrderType = '1' and hed.OrderNo = cl.OrderNo

Question User Over a year ago

I need one more clarification check my updated question now i used like this hed.OrderNo so hedging table column will be updated right ?

sqluser Over a year ago

Yes the columns your specifying in SET part are all get updated.

Collectives™ on Stack Overflow

Mysql update using select

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related