Syntax error (missing operator) in query expression when update table using other table records

Question

When trying to execute this SQL statement, I get error :

Syntax error (missing operator) in query expression Orders.BuyPrice" From Pro INNER JOIN Orders On Pro.ProID = Orders.ProID

Trying to Update Balance and BuyPrice in Pro Table from Orders table

dim query as string = "update Pro set Pro.Balance = Pro.Balance + Orders.Qu," & _
"Pro.BuyPrice = Orders.BuyPrice" & _
" From Pro INNER JOIN Orders On Pro.ProID = Orders.ProID " & _
"AND orders.OrderID = " & orderID

execute(query)

Execute:

execute(q as string)
connectDB
dim cmd as new ODBCCommand(q, DBcon) 
cmd.executeNonQuery()

I don't know what is wrong in query?

What database (DBMS) you're using? Is it SQL Server or MySQL or something else? — har07, Jun 22 '14 at 11:50
Possible duplicate of http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql — Steve, Jun 22 '14 at 12:06

user3767883 · Accepted Answer · 2014-06-23T15:32:46.547

1

ODBC use MySql so try this code

update 
      Pro 
Inner join orders 
      on 
Pro.ProID = Orders.ProID
      set 
Pro.Balance = Pro.Balance + Orders.Qu,
Pro.BuyPrice = Orders.BuyPrice
      where 
orders.OrderID = orderID

edited Jun 23 '14 at 15:32

answered Jun 23 '14 at 14:43

user3767883

53
4

This post is being automatically flagged as low quality because it is so short / only code. Would you mind expanding it by adding some text to explain how it solves the problem? – gung - Reinstate Monica Jun 23 '14 at 15:24
What do you mean by "ODBC use MySql"? – Lasse V. Karlsen Jun 23 '14 at 15:35
1

user use odbc connction from this line in his code dim cmd as new ODBCCommand(q, DBcon) , so he must use SQL statments like in MySQL – user3767883 Jun 23 '14 at 15:39

score 0 · Answer 2 · answered Jun 22 '14 at 11:06

0

You can not do a join within an update query. You have to do a select query first to retrieve Orders.Qu and Orders.BuyPrice and use the result in your update query which will looks like:

dim query as string = "update Pro set Pro.Balance = Pro.Balance + " & qu & ", Pro.BuyPrice = " & buy_price & ";"

answered Jun 22 '14 at 11:06

Elioty

81
6

thanks for reply but in this case i must loop on orders to read Qu and price , i don't need that. i believe i can do it in one query like discussed here [link](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server/2334741#2334741) but i failed to implement it – E R Jun 22 '14 at 11:16

score 0 · Answer 3 · edited Jan 07 '16 at 19:35

0

This query worked with me

Query = " update Pro Inner join orders on Pro.ProID = Orders.ProID " & _
"set Pro.Balance = Pro.Balance + Orders.Qu," & _
"Pro.BuyPrice = Orders.BuyPrice" & _
" where orders.OrderID = " & orderID

edited Jan 07 '16 at 19:35

marc_s

732,580
175
1,330
1,459

answered Jun 22 '14 at 12:24

E R

480
1
8
20

Syntax error (missing operator) in query expression when update table using other table records

3 Answers3