CODEWITHSUNDEEP
mysqlsql
saberfan7
saberfan7

Reputation: 35

What's wrong with my UPDATE statement?

I'm learning SQL, and I'm trying to import data from one table to another. I've been having some success, but the update statement is giving me issues. I have a batting average column in the new table, and I'm trying to get the actual values into it.

ie. hits (h) and at bats (ab) are in table 1 but I want h/ab = avg in table 2.

I tried: 

UPDATE seasonbatting s 

Set OBP = (H/AB) 

FROM Batting b 

WHERE b.playerID = s.playerID 

AND b.yearID = s.yearID;

but I get an error:

"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 batting b WHERE s.playerID = b.playerID AND s.yearID = p.yearID' at line 3"

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

MySQL does not support a FROM clause in the UPDATE. Your syntax looks more like Postgres or SQL Server.

You want:

UPDATE seasonbatting s JOIN
       Batting b
       ON b.playerID = s.playerID AND b.yearID = s.yearID
    Set s.OBP = (H/AB)

Upvotes: 2

Related Questions