mysql update query with sub query

by

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

More Related Contents:

  1. get first occurrence of last changed value of a column [closed]
  2. MySQL – Rows to Columns
  3. ROW_NUMBER() in MySQL
  4. multiple query same table but in different columns mysql
  5. How can I make SQL case sensitive string comparison on MySQL?
  6. 1052: Column ‘id’ in field list is ambiguous
  7. How can I temporarily disable a foreign key constraint in MySQL?
  8. How to do a batch insert in MySQL
  9. Count the occurrences of DISTINCT values
  10. Ordering by the order of values in a SQL IN() clause
  11. Creating a “Numbers Table” in MySQL
  12. SQL Selecting from two Tables with inner join and limit [duplicate]
  13. MySQL Group by SUM
  14. Set NOW() as Default Value for datetime datatype?
  15. In SQL, how to select the top 2 rows for each group
  16. Mysql query to dynamically convert rows to columns on the basis of two columns
  17. MySQL SELECT DISTINCT multiple columns
  18. SQL DELETE with JOIN another table for WHERE condition
  19. mysql trigger stored trigger is already used by statement which invoked stored trigger
  20. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  21. MySQL: How do I join same table multiple times?
  22. How to set a MySQL row to READ-ONLY?
  23. Mysql: Order by like?
  24. Getting first day of the week in MySql using Week No
  25. Using DISTINCT and COUNT together in a MySQL Query
  26. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  27. How do you force mysql LIKE to be case sensitive? [duplicate]
  28. #1060 – Duplicate column name ‘id’
  29. In what order are MySQL JOINs evaluated?
  30. Update multiple rows with multiple ‘where’ clauses for each individual row

Leave a Comment