Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 2 down vote accepted

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) 
  ON B.id = A.id 
SET A.Freq = B.idcount

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

And here is a similar discussion for SQL Server: http://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).

share|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 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 Elder Jan 8 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 at 16:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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