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


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

1 Answer 1


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).

  • You may want to add that UPDATE statements (and especially those involving joins) are quite difficult to be written in a portable way. Jan 8, 2014 at 13:31
  • The join is ANSI-style, but the UPDATE as a whole is MySQL syntax. @ypercube Is it true that the only ANSI-compliant way to update one table from another is to use subqueries in the SET clause? It's horribly verbose. Jan 8, 2014 at 16:05
  • @IainElder I have to check but I think that in the latest standards, you can also update (with certain restrictions) a view, a derived table or a CTE, i.e.: UPDATE (SELECT a.*, b.col2 FROM a JOIN b ON a.bid = b.bid) x SET col = col + col2; Jan 8, 2014 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.