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)

Yes, it is possible. It is called [`MERGE`](https://msdn.microsoft.com/en-us/library/bb510625.aspx). — Lukasz Szozda, Mar 07 '16 at 15:05
I don't see how @Fredou gets what he wants with a `MERGE` statement. — Philip Kelley, Mar 07 '16 at 15:17
@lad2025 i'm currently looking at merge and i'm not yet sure how to do it with table hints and pickup the row that was locked and not found, currently i'm only doing one generic check for both — Fredou, Mar 07 '16 at 15:30

score 0 · Accepted Answer · answered Mar 07 '16 at 15:16

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.

that is exactly the behavior i saw when i tried it with a update with different type of join, i'm currently looking at merge and not sure, like you said, if it's possible. — Fredou, Mar 07 '16 at 15:32

how to properly merge these 2 query into one update?

1 Answers1