Why doesn't SQL Server fail when the result of an UPDATE is ambiguous?

Question

I have two tables, a destination for the update:

create table dest (value int)
insert into dest values (0)

and a source:

create table source (value int)
insert into source values (4)
insert into source values (1)

If I run this query:

UPDATE dest SET value = (select value from source WHERE 1=1)

SQL Server fails with:

Subquery returned more than 1 value. This is not permitted when 
the subquery follows =, !=, <, <= , >, >= ...

which is perfect. But if I run this query:

UPDATE dest SET value = source.value FROM dest INNER JOIN Source ON 1=1

... it picks randomly one of the values from source and updates dest with it.

Scary? Is there an explanation for this?

Answer 1

Yes the reason your first query is failing has nothing to do with an update statement run this query:

select * from dest
where value = (select value from source)

When you have a subquery which is using any of the operators such as =, != etc...you cannot return more then one result. If you want to say give me all the values in dest where a matching value is in source then you would use the In clause:

select * from dest
where value in (select value from source)

As for your second part of your question, well a cell can only have a single value so what your doing is replacing it over and over again. This is perfectly valid.

As you indicated there is no way to determine which row will be choosen, which does make this interesting, especially considering that if memory serves different versions of SQL choose different rows (older versions I think used the last row where as now they use the first row).

Answer 2

In the first example the subquery returns multiple rows and the update fails. In the second example the join succeeds so the update can proceed. The results are unpredictable because the join isn't constrained.

Answer 3

Oracle does forbid the queries like this:

UPDATE dest SET value = source.value FROM dest INNER JOIN Source ON 1=1

, if the source table is not a key-preserved table (i. e. you join dest on some field from source that is not explicitly declared UNIQUE (by a UNIQUE INDEX or a PRIMARY KEY).

This is a method to ensure that at most one row from dest will be selected in the view.

If there is no UNIQUE constraint of any kind, the update will fail anyway, even if there are no actual duplicates in source.

SQL Server does not have this limitation, and just updates to the first value in encounters, skipping the others.

Which value is first depends on several conditions including join method chosen by the optimizer.