Syntax error "near FROM" when using UPDATE with JOIN in MySQL?

Question

UPDATE bestall SET view = t1.v, rawview = t1.rv 

FROM bestall INNER JOIN beststat as t1

ON bestall.bestid = t1.bestid

this query gives a syntax error of about

 'FROM bestall INNER JOIN beststat as t1 ON bestall.bestid = t1.bestid' at line 3

any reasons?

Answer 1

This is not valid MySQL syntax. However, this is true in MS SQL Server. For MySQL use:

UPDATE 
  bestall
  JOIN beststat AS t1 ON bestall.bestid = t1.bestid 
SET view = t1.v, rawview = t1.rv

MySQL requires update tables to come before a sentence SET. See MySQL UPDATEsyntax for details .

Answer 2

Try this:

UPDATE bestall INNER JOIN beststat as t1
ON bestall.bestid = t1.bestid SET view = t1.v, rawview = t1.rv