silly questions silly questions - 10 days ago 6
SQL Question

SQL query to update table

Might be stupid example but curious to know how we can do it with SQL query. I am using MySQL but trying to write query db independent way.

I have following table,

Course
---------
Id | lang | batch | flag
===========================
1 | Java | A | null
2 | Cpp | B | null
3 | Java | A | null
4 | Java | C | null
5 | Java | C | null


Query is, if two records found for lang = Java with same batch then set flag = 0 for first entry and set flag = 1 for second entry in table.

So output for above query would look like following,

Course
---------
Id | lang | batch | flag
===========================
1 | Java | A | 0
2 | Cpp | B | null
3 | Java | A | 1
4 | Java | C | 0
5 | Java | C | 1


How I can write SQL query for above example. Thanks for help :)

EDIT :
Sorry for late edit, there is one addition like if there is only one record found for lang = java and any batch then set flag to 0.
Is it possible to write both queries in single query?

Answer

The following should work correctly assuming that a given matching batch will only have two records:

UPDATE Course t1
INNER JOIN
(
    SELECT batch
    FROM Course
    GROUP BY batch
    HAVING SUM(CASE WHEN lang = 'Java' THEN 1 ELSE 0 END) = 2
) t2
    ON t1.batch = t2.batch
INNER JOIN
(
    SELECT batch, MIN(Id) AS minId
    FROM Course
    GROUP BY batch
) t3
    ON t2.batch = t3.batch
SET t1.flag = CASE WHEN t1.Id = t3.minId THEN 0 ELSE 1 END

Demo here:

SQLFiddle

Note: The above Fiddle does a SELECT to show which records would be updated, and what the old and new flag values would be for each record.

The exact JOIN syntax you would need to use is database-specific. I gave the syntax for MySQL, but the query should not change much in going to another RDBMS such as SQL Server, so long as it supports join updates.