Is the behavior of this UPDATE SQL expected or a Bug?

Hi All,
I am using SQLServer 2000 with ServicePack 3a. Can any one tell me if the
following Update SQLis a bug or expected behavior?
CREATE TABLE [Table1] (
[col1] [int] NULL ,
[col2] [int] NULL
)
GO
CREATE TABLE [Table2] (
[col1] [int] NULL ,
[col3] [int] NULL
)
GO
insert into table1 (col1, col2) values (1,null)
insert into table1 (col1, col2) values (2,null)
insert into table2 (col1, col3) values (1,11)
insert into table2 (col1, col3) values (1,12)
insert into table2 (col1, col3) values (2,22)
go
-- Here is the UPDATE SQL. I am trying to update col2 of Table1 with
-- col3 of Table2
update table1
set col2=b.col3
from table1 a, table2 b
where a.col1=b.col1
I was expecting that the above SQL should fail because in this JOIN between
two tables there are two rows fetched from table2 for first row in table1
But against to my expectations that SQL was successful and got message
(2 row(s) affected)
And first row in table1, it has updated col2 with value 12
Am I making sense?
Thanks in advance,
Vinod"VM" <VM@.discussions.microsoft.com> wrote in message
news:4F375926-71A9-4CCD-9119-7D336F7809A4@.microsoft.com...
> Hi All,
> I am using SQLServer 2000 with ServicePack 3a. Can any one tell me if the
> following Update SQLis a bug or expected behavior?
> CREATE TABLE [Table1] (
> [col1] [int] NULL ,
> [col2] [int] NULL
> )
> GO
> CREATE TABLE [Table2] (
> [col1] [int] NULL ,
> [col3] [int] NULL
> )
> GO
> insert into table1 (col1, col2) values (1,null)
> insert into table1 (col1, col2) values (2,null)
> insert into table2 (col1, col3) values (1,11)
> insert into table2 (col1, col3) values (1,12)
> insert into table2 (col1, col3) values (2,22)
> go
> -- Here is the UPDATE SQL. I am trying to update col2 of Table1 with
> -- col3 of Table2
> update table1
> set col2=b.col3
> from table1 a, table2 b
> where a.col1=b.col1
>
> I was expecting that the above SQL should fail because in this JOIN
> between
> two tables there are two rows fetched from table2 for first row in table1
> But against to my expectations that SQL was successful and got message
> (2 row(s) affected)
>
> And first row in table1, it has updated col2 with value 12
> Am I making sense?
> Thanks in advance,
> Vinod
>
You are absolutely right to be concerned. Unfortunately this is the expected
behaviour. Books Online:
"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."
So you get random results by design! If you want to use the UPDATE FROM
syntax then be very sure your join returns unique rows. If in doubt you may
prefer to use the ANSI standard syntax, which does fail safe:
UPDATE table1
SET col2 =
(SELECT col3
FROM table2
WHERE col1 = table1.col1);
Result:
Server: 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.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Vinod,
The first thing to understand about this situation is that the ANSI
standard does not allow a FROM clause in an UPDATE command. The
reason it does not is the ambiguity that you are asking about - if
there are two matching rows, which one provides the value?
The answer is that when using a FROM clause in an UPDATE, and joining
to a table with multiple rows matching a single row being updated, you
can not predict which row will contribute the value that ends up in
the matching row.
I have not tested in recent years, but at least in release 6.5 and
beyond there would have been TWO rows in the log, one for each match,
even though the "row(s) affected" would only count the row updated
once. That used to be - may still be - a great way to make the log
get really big very quickly; update OrderMaster by joining to
OrderItem, with an average of ten items per master, and log ten
updates for each row.
Hope that helps.
Roy
On Wed, 15 Feb 2006 15:55:27 -0800, "VM"
<VM@.discussions.microsoft.com> wrote:

>Hi All,
>I am using SQLServer 2000 with ServicePack 3a. Can any one tell me if the
>following Update SQLis a bug or expected behavior?
>CREATE TABLE [Table1] (
> [col1] [int] NULL ,
> [col2] [int] NULL
> )
>GO
>CREATE TABLE [Table2] (
> [col1] [int] NULL ,
> [col3] [int] NULL
> )
>GO
>insert into table1 (col1, col2) values (1,null)
>insert into table1 (col1, col2) values (2,null)
>insert into table2 (col1, col3) values (1,11)
>insert into table2 (col1, col3) values (1,12)
>insert into table2 (col1, col3) values (2,22)
>go
>-- Here is the UPDATE SQL. I am trying to update col2 of Table1 with
>-- col3 of Table2
>update table1
>set col2=b.col3
>from table1 a, table2 b
>where a.col1=b.col1
>
>I was expecting that the above SQL should fail because in this JOIN between
>two tables there are two rows fetched from table2 for first row in table1
>But against to my expectations that SQL was successful and got message
>(2 row(s) affected)
>
>And first row in table1, it has updated col2 with value 12
>Am I making sense?
>Thanks in advance,
>Vinod
>|||David and Roy,
Thankyou so much for your replies. Now I have good picture about the FROM
clause in UPDATE statement. I will be very careful from now.
Again, Thanks guys!!
Vinod
"Roy Harvey" wrote:

> Vinod,
> The first thing to understand about this situation is that the ANSI
> standard does not allow a FROM clause in an UPDATE command. The
> reason it does not is the ambiguity that you are asking about - if
> there are two matching rows, which one provides the value?
> The answer is that when using a FROM clause in an UPDATE, and joining
> to a table with multiple rows matching a single row being updated, you
> can not predict which row will contribute the value that ends up in
> the matching row.
> I have not tested in recent years, but at least in release 6.5 and
> beyond there would have been TWO rows in the log, one for each match,
> even though the "row(s) affected" would only count the row updated
> once. That used to be - may still be - a great way to make the log
> get really big very quickly; update OrderMaster by joining to
> OrderItem, with an average of ten items per master, and log ten
> updates for each row.
> Hope that helps.
> Roy
>
> On Wed, 15 Feb 2006 15:55:27 -0800, "VM"
> <VM@.discussions.microsoft.com> wrote:
>
>