Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql update query with sub query

Tags:

sql

mysql

Can anyone see what is wrong with the below query?

When I run it I get:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a where a.CompetitionID = Competition.CompetitionID' at line 8

Update Competition Set Competition.NumberOfTeams = ( SELECT count(*) as NumberOfTeams FROM PicksPoints where UserCompetitionID is not NULL group by CompetitionID ) a where a.CompetitionID =  Competition.CompetitionID 
like image 745
user1542043 Avatar asked Jul 21 '12 01:07

user1542043


People also ask

Can we use subquery in UPDATE statement in MySQL?

The subquery either returns a single row, or else has no correlated column references. The subquery is in the UPDATE statement WHERE clause, using Condition with Subquery syntax. No SPL routine in the subquery can reference the same table that UPDATE is modifying.

Can we use UPDATE and SELECT together?

The subquery defines an internal query that can be used inside a SELECT, INSERT, UPDATE and DELETE statement. It is a straightforward method to update the existing table data from other tables. The above query uses a SELECT statement in the SET clause of the UPDATE statement.

Which one is correct syntax for subqueries with the UPDATE statement?

Subqueries with the UPDATE Statement The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement. The basic syntax is as follows.


2 Answers

The main issue is that the inner query cannot be related to your where clause on the outer update statement, because the where filter applies first to the table being updated before the inner subquery even executes. The typical way to handle a situation like this is a multi-table update.

Update   Competition as C   inner join (     select CompetitionId, count(*) as NumberOfTeams     from PicksPoints as p     where UserCompetitionID is not NULL     group by CompetitionID   ) as A on C.CompetitionID = A.CompetitionID set C.NumberOfTeams = A.NumberOfTeams 

Demo: http://www.sqlfiddle.com/#!2/a74f3/1

like image 103
mellamokb Avatar answered Sep 28 '22 22:09

mellamokb


Thanks, I didn't have the idea of an UPDATE with INNER JOIN.

In the original query, the mistake was to name the subquery, which must return a value and can't therefore be aliased.

UPDATE Competition SET Competition.NumberOfTeams = (SELECT count(*) -- no column alias   FROM PicksPoints   WHERE UserCompetitionID is not NULL   -- put the join condition INSIDE the subquery :   AND CompetitionID =  Competition.CompetitionID   group by CompetitionID ) -- no table alias 

should do the trick for every record of Competition.

To be noticed :

The effect is NOT EXACTLY the same as the query proposed by mellamokb, which won't update Competition records with no corresponding PickPoints.

Since SELECT id, COUNT(*) GROUP BY id will only count for existing values of ids,

whereas a SELECT COUNT(*) will always return a value, being 0 if no records are selected.

This may, or may not, be a problem for you.

0-aware version of mellamokb query would be :

Update Competition as C LEFT join (   select CompetitionId, count(*) as NumberOfTeams   from PicksPoints as p   where UserCompetitionID is not NULL   group by CompetitionID ) as A on C.CompetitionID = A.CompetitionID set C.NumberOfTeams = IFNULL(A.NumberOfTeams, 0) 

In other words, if no corresponding PickPoints are found, set Competition.NumberOfTeams to zero.

like image 41
Pierre-Olivier Vares Avatar answered Sep 28 '22 23:09

Pierre-Olivier Vares