Why doesn't SQL Server work if the result of UPDATE is mixed?

Question

I have two tables, the destination for the update:

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

and 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 Crash:

 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 

... he randomly selects one of the values ​​from the source and updates it.

Scary? Is there any explanation for this?

Answer 1

Yes, the reason your first request fails has nothing to do with the update statement, run this request:

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

When you have a subquery that uses any of the operators, such as =,! =, Etc., you cannot return more than one result. If you want to say, give me all the values ​​in dest, where the corresponding value is in the source, you should use the In clause:

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

As for your second part of your question, the cell can have only one value, so your actions replace it again and again. This is absolutely true.

As you pointed out, there is no way to determine which row will be selected, which makes it interesting, especially considering that if the memory serves different versions of SQL, select different lines (older versions, I think, used the last line, where now they use the first line )

Answer 2

Oracle forbids such requests:

 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 attach dest to some field from source that is not explicitly declared by UNIQUE (via UNIQUE INDEX or PRIMARY KEY ).

This is a method of ensuring that no more than one row from dest is selected in the view.

If there is no UNIQUE restriction of any type, the update will still work, even if there are no actual duplicates in source .

SQL Server does not have this limitation and simply updates the first value in the meetings, skipping the rest.

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

Answer 3

In the first example, the subquery returns multiple rows and the update fails. In the second example, the connection completed successfully, so the update can continue. The results are unpredictable because the connection is not limited.