SQL select from table with an update subquery-CodePudding

I'm trying to update a table and at the same time (in the same transaction) select from the table and perform a count on one of its columns.

See below for example code. The last 2 querys (the UPDATE and the SELECT) I want to merge them into one query somehow.

CREATE TABLE ChildProcesses
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    ParentId INT,
    ProcessIsFinished BIT
);

INSERT INTO ChildProcesses (ParentId, ProcessIsFinished) VALUES (1234, 0);
INSERT INTO ChildProcesses (ParentId, ProcessIsFinished) VALUES (1234, 0);
INSERT INTO ChildProcesses (ParentId, ProcessIsFinished) VALUES (1234, 0);
INSERT INTO ChildProcesses (ParentId, ProcessIsFinished) VALUES (1234, 0);

UPDATE ChildProcesses SET ProcessIsFinished = 1 WHERE Id = 4;

SELECT
    COUNT(*) AS CountProcessesStillRunning
FROM
    ChildProcesses
WHERE
    ParentId = 1234 AND
    ProcessIsFinished = 0;

The reason for all of this is, I have a distributed process where my code is running in many places. This code performs processing on my "ChildProcesses" and when they're done they set the "ChildProcesses.ProcessIsFinished" to "1". When all of the child processes have finished I need to perform some cleanup steps, but only when ALL of the child processes have finished and I only want to perform the cleanup steps once. So I'm trying to find a way to both "mark the process as finished" and at the same time "check to see if this was the last child process to finish". If this is the last child process to finish then i'll have that child process perform the cleanup steps.

I thought to have the UPDATE query as a subquery to the SELECT, but I get errors saying that's not allowed. Here's what I tried that's not working:

SELECT
    COUNT(*) AS CountProcessesStillRunning
FROM
    ChildProcesses
WHERE
    ParentId = (UPDATE ChildProcesses SET ProcessIsFinished = 1 OUTPUT INSERTED.ParentId WHERE Id = 4) AND
    ProcessIsFinished = 0;

Any help would be appreciated.

CodePudding user response：

I couldn't find out how to run an UPDATE as a subquery to a SELECT. But @P.Salmon gave a good alternative that works for my case.

Basically, I'm going to add a column to the parent table IsCleanedUp and so after running the update and select statements (to determine if all processes have finished) I'll run another update on the parent to mark the full process as finished and determine if I'm the last child process running.

Something like the following:

UPDATE
    ParentProcesses
SET
    IsCleanedUp = 1
OUTPUT
    DELETED.IsCleanedUp AS IsCleanedUpDeleted
WHERE
    Id = 1234;

Then, if IsCleanedUpDeleted is 0 then I'd have that child process perform the cleanup steps, if it's 1 then I'd know another child process was going to perform the cleanup steps.

CodePudding user response：

You cannot nest UPDATE...OUTPUT except inside a INSERT...SELECT, you are not allowed a bare SELECT and you are not allowed to group.

Instead you can use a table variable

DECLARE @output TABLE (ParentId int);

UPDATE ChildProcesses
SET ProcessIsFinished = 1
OUTPUT inserted.ParentId
  INTO @output (ParentId)
WHERE Id = 4;

SELECT
    COUNT(*) AS CountProcessesStillRunning
FROM
    ChildProcesses cp
JOIN
    @output u ON u.ParentId = cp.ParentId
WHERE
    cp.ProcessIsFinished = 0;

If ParentId is actually the primary key for this table, and you just want the number of rows affected then you can just use @@ROWCOUNT. It doesn't seem that you actually want this logic, but you should get the idea.

UPDATE ChildProcesses
SET ProcessIsFinished = 1
WHERE Id = 4
  AND ProcessIsFinished = 0;

SELECT
    @@ROWCOUNT AS CountProcessesStillRunning;