UPDATE a column by COUNT of another table

It might be a naive question, but what is the difference of these two queries and which is preferred?

UPDATE table1, 
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B 
SET table1.Freq = B.idcount WHERE table1.id=B.id

and

UPDATE table1 A INNER JOIN 
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) B USING (id) 
SET A.Freq = B.idcount

Same query. different join syntax.

The first is old style ANSI, the latter is later ANSI. If you must pick between them, pick the latter.

I wouldn't use either of them due to the limitations of USING (can't be aliased). Instead write as:

UPDATE table1 A 
INNER JOIN (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) as B
  ON B.id = A.id 
SET A.Freq = B.idcount

See this Oracle answer as to why USING is not to be preferred: https://stackoverflow.com/questions/456684/mixing-using-and-on-in-oracle-ansi-join

And here is a similar discussion for SQL Server: https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax

In general, get used to writing SQL syntax in a way that is portable. While you may never port your code, you will at least be able to port your skills around to multiple database (Which is generally a cool thing to do to avoid becoming a one-trick pony).

StackExchange Database Administrators Q#56323, answer score: 5