CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
Joe Ruder
Joe Ruder

Reputation: 2194

SQL Update based on a specific count from a child table

Using Microsoft Azure SQL I have the following select query working. I need a update where I set wd.delete_requested = true for any records with a stopsCount = 0. So in other words, the stop_detail is related to the Driver_Wave_Ticket_Table. I need to flag for deletion any wave ticket record that does not have any stop_detail records.

    SELECT wd.Id,
(SELECT COUNT(*) FROM Stop_Detail sd WHERE sd.wave_ticket_detail_Id = wd.Id) stopsCount
FROM Driver_Wave_Ticket_Detail wd
WHERE  wd.wave_ticket_date = '1901-01-01 00:00:00.000' 
AND wd.delete_requested IS NULL
AND wd.opened_utc IS  NULL
ORDER BY wd.id, stopsCount

Upvotes: 1

Views: 33

Answers (2)

Jiaks
Jiaks

Reputation: 21

Not sure if this answers your question, but I'm thinking of using a case statement for this scenario:

SELECT
a.Id,
a.stopsCount,
CASE WHEN stopsCount = 0 THEN True else FALSE end as delete_flag
FROM
(SELECT wd.Id,
(SELECT COUNT(*) FROM Stop_Detail sd WHERE sd.wave_ticket_detail_Id = wd.Id) stopsCount
FROM Driver_Wave_Ticket_Detail wd
WHERE  wd.wave_ticket_date = '1901-01-01 00:00:00.000' 
AND wd.delete_requested IS NULL
AND wd.opened_utc IS  NULL) a
ORDER BY
a.Id,
a.stopsCount

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Hmm . . . I'm thinking not exists:

update wd
    set delete_requested = 1
    from Driver_Wave_Ticket_Detail wd
    where not exists (select 1
                      from Stop_Detail sd 
                      where sd.wave_ticket_detail_Id = wd.Id
                     ) and
          wd.wave_ticket_date = '1901-01-01 00:00:00.000' and
          wd.delete_requested IS NULL and
          wd.opened_utc IS  NULL;

In general, not exists is faster than doing an aggregation and checking that the result is 0.

Upvotes: 1

Related Questions