What is wrong with this UPDATE query?

Question

I am attempting to update a table column with values from another table + a little arithmetic. I am no expert but reading a few questions seems like I have to use JOIN on the COMMON column to select data from both tables.

UPDATE Farming SET Farming.GoldLabor = Farming.GoldPerUnit / (DataTable.LaborYieldA + 
DataTable.LaborYieldB) 
FROM Farming f INNER JOIN DataTable d ON f.name = d.name;

Error is:

#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 Farming f INNER JOIN 
DataTable d ON f.name = d.name' at line 1

I am using code from a few other questions answered here.

https://stackoverflow.com/a/707383

https://stackoverflow.com/a/1746161

I am using mySQL from 1&1 Hosting, and I attempted to use JOIN & INNER JOIN.

Thanks for your time.

Can you post your actual query? The error has `LIMIT` in it, but your posted code does not. — sgeddes, Sep 23 '14 at 22:04
Sorry, must have been the error while trying something else. I updated the error to what it shows with the query on the question. — Juan, Sep 23 '14 at 22:07
Looks like a duplicate: http://stackoverflow.com/questions/15209414/mysql-update-join — sgeddes, Sep 23 '14 at 22:12

score 2 · Accepted Answer · answered Sep 23 '14 at 22:09

2

MySQL supports multi-table UPDATE syntax with joins in it, but not like you are doing. The syntax you show may work on Microsoft SQL Server, but not on MySQL.

This should work:

UPDATE Farming f INNER JOIN DataTable d ON f.name = d.name
SET f.GoldLabor = f.GoldPerUnit / (d.LaborYieldA + d.LaborYieldB);

answered Sep 23 '14 at 22:09

Bill Karwin

538,548
86
673
828

It does thank you, I do prefer this syntax a little before because I state what I will be working with first and then I use it. I need to pay more attention to what query "engine" I am using. – Juan Sep 23 '14 at 22:13
For what it's worth, neither MySQL nor Microsoft are wrong, because multi-table UPDATE is not in the standard SQL specification. Both vendors are doing it as extensions to the standard. – Bill Karwin Sep 23 '14 at 22:15

score 0 · Answer 2 · answered Sep 23 '14 at 22:19

0

Try this:

UPDATE farming as f
 INNER JOIN DataTable d ON f.name = d.name
   SET f.GoldLabor = f.GoldPerUnit / (DataTable.LaborYieldA + DataTable.LaborYieldB) ;

SQLFiddle

answered Sep 23 '14 at 22:19

neshkeev

6,280
3
26
47

What is wrong with this UPDATE query?

2 Answers2