Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Update produces impossible result

The following produces an illogical result:

DECLARE @Target table
(
c1 integer PRIMARY KEY,
c2 integer NOT NULL,
c3 integer NOT NULL
);

DECLARE @Source table
(
c1 integer NULL,
c2 integer NULL,
c3 integer NULL,

INDEX c CLUSTERED (c1)
);

INSERT @Target
(c1, c2, c3)
VALUES
(1, 0, 0);

INSERT @Source
(c1, c2, c3)
VALUES
(1, 2, NULL),
(1, NULL, 3),
(1, 4, 4);

UPDATE T
SET T.c2 = S.c2,
T.c3 = S.c3
FROM @Target AS T
JOIN @Source AS S
ON S.c1 = T.c1;

SELECT * FROM @Target AS T;

Result:

c1 c2 c3
1 2 3

The update is non-deterministic, but it should not produce a result that comes from a mixture of matched rows. It should throw an error in this case because the target does not accept nulls in any row, and the matching source rows all have a null.

27 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Paul White shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

4 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base