Bug in UPDATE statement and 1 : n cardinality ?

The problem is in one-to-many cardinality and UPDATE statement :
( SQL server 2005 developer edition )

create table #table1( id int, firstCol int)
insert #table1 values ( 1,0)
insert #table1 values ( 2,0)
go

create table #table2( id int, secondCol int)
insert #table2 values ( 1,10)
insert #table2 values ( 1,20)
insert #table2 values ( 1,30)
insert #table2 values ( 3,100)
go

--

1. shape UPDATE

update #table1 set firstCol = firstCol +( select secondCol from #table2
where #table1.id = #table2.id )

return error message :

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

2. shape UPDATE

update r set firstCol = firstCol + s.secondCol
from #table1 r
join ( select id,secondCol from #table2 ) s
on r.id = s.id

return message : (1 row(s) affected)
and #table1 is updated only one of row ( generally random ) from #table2 for ID = 1

select * from #table1

id firstCol
-- --
1 10
2 0

3. shape UPDATE

update #table1 set firstCol = firstCol + secondCol
from #table2 where #table1.id = #table2.id

return message : (1 row(s) affected)
and #table1 is updated only one of row ( generally random ) from #table2 for ID = 1

--

Is it normal ? I expected error message or summary value for ID = 1 from #table2 .

The behavior of SQL Server's proprietary UPDATE .. FROM statement

is undefined in this situation, and this is documented in Books

Online:

UPDATE (Transact-SQL)

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/40e63302-0c68-4593-af3e-6d190181fee7.htm

The results of an UPDATE statement are undefined if the statement

includes a FROM clause that is not specified in such a way that

only one value is available for each column occurrence that is

updated, that is if the UPDATE statement is not deterministic.

For example, in the UPDATE statement in the following script,

both rows in Table1 meet the qualifications of the FROM clause

in the UPDATE statement; but it is undefined which row from

Table1 is used to update the row in Table2.

Similar language appears in the article

Changing Data by Using the FROM Clause

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f4b7f060-f21b-4117-93cb-c7a7cc75569c.htm

Steve Kass

Drew University

http://www.stevekass.com

Jiri Lichtenberg@.discussions.microsoft.com wrote:

>

>

> The problem is in one-to-many cardinality and UPDATE statement :

> ( SQL server 2005 developer edition )

>

> create table #table1( id int, firstCol int)

> insert #table1 values ( 1,0)

> insert #table1 values ( 2,0)

> go

>

> create table #table2( id int, secondCol int)

> insert #table2 values ( 1,10)

> insert #table2 values ( 1,20)

> insert #table2 values ( 1,30)

> insert #table2 values ( 3,100)

> go

>

>

> --

>

> 1. shape UPDATE

>

> update #table1 set firstCol = firstCol +( select secondCol from #table2

> where #table1.id = #table2.id )

>

> return error message :

>

> Msg 512, Level 16, State 1, Line 1

> Subquery returned more than 1 value. This is not permitted when the

> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an

> expression.

> The statement has been terminated.

>

> 2. shape UPDATE

>

> update r set firstCol = firstCol + s.secondCol

> from #table1 r

> join ( select id,secondCol from #table2 ) s

> on r.id = s.id

>

> return message : (1 row(s) affected)

> and #table1 is updated only one of row ( generally random ) from #table2

> for ID = 1

>

> select * from #table1

>

> id firstCol

> -- --

> 1 10

> 2 0

>

> 3. shape UPDATE

>

> update #table1 set firstCol = firstCol + secondCol

> from #table2 where #table1.id = #table2.id

>

> return message : (1 row(s) affected)

> and #table1 is updated only one of row ( generally random ) from #table2

> for ID = 1

>

>

> --

>

> Is it normal ? I expected error message or summary value for ID = 1

> from #table2 .

>

>

>

>