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

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

| improve this answer | |
  • You may want to add that UPDATE statements (and especially those involving joins) are quite difficult to be written in a portable way. – ypercubeᵀᴹ Jan 8 '14 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. – Iain Samuel McLean Elder Jan 8 '14 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; – ypercubeᵀᴹ Jan 8 '14 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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