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).