how to properly merge these 2 query into one update?

Question

This currently work but I would like to change the update statement to include the action of the insert below it, is it posssible?

UPDATE cas
    SET [Locked] = CASE WHEN cas.Locked <> @TargetState AND cas.LastChanged = filter.SourceDateTime THEN @TargetState ELSE cas.[Locked] end,
    OUTPUT inserted.Id, inserted.Locked, CASE WHEN inserted.Locked = @TargetState AND 
                                                   inserted.LastChanged = filter.SourceDateTime THEN 1 
                                              WHEN inserted.LastChanged <> filter.SourceDateTime THEN -1 -- out of sync
                                              WHEN deleted.Locked = @TargetState THEN -2 -- was not in a good state
                                              ELSE 0 END --generic failure
    INTO @OUTPUT
    FROM dbo.Target cas WITH(READPAST, UPDLOCK, ROWLOCK) INNER JOIN  @table filter ON cas.Id = filter.Id 

INSERT INTO @OUTPUT
SELECT filter.id, NULL, when cas.id is not null -3 --  row was/is locked
                                           else -4 end --not found 
FROM  @table filter left join dbo.target cas with(nolock) on filter.id = cas.id
WHERE NOT EXISTS (SELECT 1 FROM @OUTPUT result WHERE filter.id = result.UpdatedId)

Answer 1

I do not think what you want is possible.

• You start with a table to be updated. Let’s say this table contains a set of IDs, say, 1 to 6
• You join onto a temp table containing a different set of IDs that may partially overlap (say, 4 to 9)
• You issue the update using an inner join. Only rows 4 to 6 are updated
• The output clause picks up data only for modified rows, so you only get data for rows 4 to 6
• If you flipped this to an outer join (such that all temp table rows are selected), you still only update rows 4 to 6, and the output clause still only kicks out data for rows 4 to 6

So, no, I see no way of achieving this goal in a single SQL statement.