Asp Forum - SELECT and UPDATE - microsoft.public.sqlserver.programming

simonZ

3/30/2007 8:41:00 AM

I have very simple example.

If I write select statement:

select c.* from table1 c INNER JOIN table2 i
ON c.warehouseID=i.warehouseID AND c.productID=i.productID

I get 3 rows:

warehouseID productID quantity
-----------------------------------------
1 'A' 10
1 'A' 20
2 'B' 30

I I write UPDATE statement:

UPDATE i SET i.quantity=i.quantity+c.quantity
from table1 c INNER JOIN table2 i
ON c.warehouseID=i.warehouseID AND c.productID=i.productID

only 2 rows are updated.
Product A is updated only for quantity=10, not for quantity=20.

Why update statement doesn't effect as many rows as select statement
returns?

Regards,Simon

9 Answers

simonZ

3/30/2007 8:57:00 AM

Here is DDL:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[table1]

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[table2]

CREATE TABLE [dbo].[table1] (
[rowID] [int] IDENTITY (1, 1) NOT NULL ,
[warehouseID] [int] NOT NULL ,
[productID] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
[quantity] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[table2] (
[warehouseID] [int] NOT NULL ,
[productID] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
[quantity] [int] NOT NULL
) ON [PRIMARY]

INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'A',10)
INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'A',20)
INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'B',30)

INSERT INTO table2(warehouseID,productID,quantity) VALUES (1,'A',5)
INSERT INTO table2(warehouseID,productID,quantity) VALUES (1,'B',100)

begin tran
select * from table2

select c.* from table1 c INNER JOIN table2 i
ON c.warehouseID=i.warehouseID AND c.productID=i.productID

UPDATE i SET i.quantity=i.quantity+c.quantity
from table1 c INNER JOIN table2 i
ON c.warehouseID=i.warehouseID AND c.productID=i.productID

select * from table2
rollback tran

Quantity in table2 should be 35, but it's not.

Regards,S

"simonZ" <simon.zupan@studio-moderna.com> wrote in message
news:O6j5wbqcHHA.1240@TK2MSFTNGP04.phx.gbl...
>I have very simple example.
>
> If I write select statement:
>
> select c.* from table1 c INNER JOIN table2 i
> ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> I get 3 rows:
>
> warehouseID productID quantity
> -----------------------------------------
> 1 'A' 10
> 1 'A' 20
> 2 'B' 30
>
> I I write UPDATE statement:
>
> UPDATE i SET i.quantity=i.quantity+c.quantity
> from table1 c INNER JOIN table2 i
> ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> only 2 rows are updated.
> Product A is updated only for quantity=10, not for quantity=20.
>
> Why update statement doesn't effect as many rows as select statement
> returns?
>
> Regards,Simon
>

Rahul

3/30/2007 8:58:00 AM

On Mar 30, 1:40 pm, "simonZ" <simon.zu...@studio-moderna.com> wrote:
> I have very simple example.
>
> If I write select statement:
>
> select c.* from table1 c INNER JOIN table2 i
> ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> I get 3 rows:
>
> warehouseID productID quantity
> -----------------------------------------
> 1 'A' 10
> 1 'A' 20
> 2 'B' 30
>
> I I write UPDATE statement:
>
> UPDATE i SET i.quantity=i.quantity+c.quantity
> from table1 c INNER JOIN table2 i
> ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> only 2 rows are updated.
> Product A is updated only for quantity=10, not for quantity=20.
>
> Why update statement doesn't effect as many rows as select statement
> returns?
>
> Regards,Simon

Hi,

In your select statement you have one to many link (ie one row link
with more than one row), warehouseID is not unique with in either a
table.

Rahul

xyb

3/30/2007 8:59:00 AM

On 3?30?, ??4?40?, "simonZ" <simon.zu...@studio-moderna.com> wrote:
> I have very simple example.
>
> If I write select statement:
>
> select c.* from table1 c INNER JOIN table2 i
> ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> I get 3 rows:
>
> warehouseID productID quantity
> -----------------------------------------
> 1 'A' 10
> 1 'A' 20
> 2 'B' 30
>
> I I write UPDATE statement:
>
> UPDATE i SET i.quantity=i.quantity+c.quantity
> from table1 c INNER JOIN table2 i
> ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> only 2 rows are updated.
> Product A is updated only for quantity=10, not for quantity=20.
>
> Why update statement doesn't effect as many rows as select statement
> returns?
>
> Regards,Simon

i have tested some code and the problem have not appeared,can you post
your DDL?

drop table #t
drop table #tt

create table #t
(
c1 int,
c3 int,
c2 int
)

create table #tt
(
c1 int,
c3 int,
c2 int
)

insert into #t
select 1,1,100
union select 1,1,200
union select 2,1,101

insert into #tt
select 1,1,200
union select 2,1,101

select a.* from #t a join #tt b on a.c1 = b.c1 and a.c3 = b.c3

update a set a.c2 = a.c2 + b.c2
from #t a join #tt b on a.c1 = b.c1 and a.c3 = b.c3

select * from #t

xyb

3/30/2007 9:26:00 AM

On 3?30?, ??4?56?, "simonZ" <simon.zu...@studio-moderna.com> wrote:
> Here is DDL:
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[table1]
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[table2]
>
> CREATE TABLE [dbo].[table1] (
> [rowID] [int] IDENTITY (1, 1) NOT NULL ,
> [warehouseID] [int] NOT NULL ,
> [productID] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
> [quantity] [int] NULL
> ) ON [PRIMARY]
>
> CREATE TABLE [dbo].[table2] (
> [warehouseID] [int] NOT NULL ,
> [productID] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
> [quantity] [int] NOT NULL
> ) ON [PRIMARY]
>
> INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'A',10)
> INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'A',20)
> INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'B',30)
>
> INSERT INTO table2(warehouseID,productID,quantity) VALUES (1,'A',5)
> INSERT INTO table2(warehouseID,productID,quantity) VALUES (1,'B',100)
>
> begin tran
> select * from table2
>
> select c.* from table1 c INNER JOIN table2 i
> ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> UPDATE i SET i.quantity=i.quantity+c.quantity
> from table1 c INNER JOIN table2 i
> ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> select * from table2
> rollback tran
>
> Quantity in table2 should be 35, but it's not.
>
> Regards,S
>
> "simonZ" <simon.zu...@studio-moderna.com> wrote in message
>
> news:O6j5wbqcHHA.1240@TK2MSFTNGP04.phx.gbl...
>
>
>
> >I have very simple example.
>
> > If I write select statement:
>
> > select c.* from table1 c INNER JOIN table2 i
> > ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> > I get 3 rows:
>
> > warehouseID productID quantity
> > -----------------------------------------
> > 1 'A' 10
> > 1 'A' 20
> > 2 'B' 30
>
> > I I write UPDATE statement:
>
> > UPDATE i SET i.quantity=i.quantity+c.quantity
> > from table1 c INNER JOIN table2 i
> > ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> > only 2 rows are updated.
> > Product A is updated only for quantity=10, not for quantity=20.
>
> > Why update statement doesn't effect as many rows as select statement
> > returns?
>
> > Regards,Simon- ??????? -
>
> - ??????? -

thank you for post DDL
you see that table2 have only two row,and UPDATE clause based on this
2 rows to update,
so if i have not misunderstood your meaning,you will want data as:
1 A 35
1 B 130
so
you can not update one row from other two row i think.
if you want, you shoud use aggerate function here :)

simonZ

3/30/2007 9:45:00 AM

Thanx.

I know I can use agregate but
if select join statement returns 3 rows, than also the same update from the
same join should affect all 3 rows.

This is very tricky and can cause a lot of mistakes over sql users, because
a lot of them doesn't know that and it's not so logical.
I think that this should be repaired with some service pack :)

regards,Simon

"xyb" <xiangyuanbo@gmail.com> wrote in message
news:1175246779.324079.169690@e65g2000hsc.googlegroups.com...
On 3ÔÂ30ÈÕ, ÏÂÎç4Ê±56·Ö, "simonZ" <simon.zu...@studio-moderna.com> wrote:
> Here is DDL:
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[table1]
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[table2]
>
> CREATE TABLE [dbo].[table1] (
> [rowID] [int] IDENTITY (1, 1) NOT NULL ,
> [warehouseID] [int] NOT NULL ,
> [productID] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
> [quantity] [int] NULL
> ) ON [PRIMARY]
>
> CREATE TABLE [dbo].[table2] (
> [warehouseID] [int] NOT NULL ,
> [productID] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
> [quantity] [int] NOT NULL
> ) ON [PRIMARY]
>
> INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'A',10)
> INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'A',20)
> INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'B',30)
>
> INSERT INTO table2(warehouseID,productID,quantity) VALUES (1,'A',5)
> INSERT INTO table2(warehouseID,productID,quantity) VALUES (1,'B',100)
>
> begin tran
> select * from table2
>
> select c.* from table1 c INNER JOIN table2 i
> ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> UPDATE i SET i.quantity=i.quantity+c.quantity
> from table1 c INNER JOIN table2 i
> ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> select * from table2
> rollback tran
>
> Quantity in table2 should be 35, but it's not.
>
> Regards,S
>
> "simonZ" <simon.zu...@studio-moderna.com> wrote in message
>
> news:O6j5wbqcHHA.1240@TK2MSFTNGP04.phx.gbl...
>
>
>
> >I have very simple example.
>
> > If I write select statement:
>
> > select c.* from table1 c INNER JOIN table2 i
> > ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> > I get 3 rows:
>
> > warehouseID productID quantity
> > -----------------------------------------
> > 1 'A' 10
> > 1 'A' 20
> > 2 'B' 30
>
> > I I write UPDATE statement:
>
> > UPDATE i SET i.quantity=i.quantity+c.quantity
> > from table1 c INNER JOIN table2 i
> > ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
> > only 2 rows are updated.
> > Product A is updated only for quantity=10, not for quantity=20.
>
> > Why update statement doesn't effect as many rows as select statement
> > returns?
>
> > Regards,Simon- Òþ²Ø±»ÒýÓÃÎÄ×Ö -
>
> - ÏÔÊ¾ÒýÓÃµÄÎÄ×Ö -

thank you for post DDL
you see that table2 have only two row,and UPDATE clause based on this
2 rows to update,
so if i have not misunderstood your meaning,you will want data as:
1 A 35
1 B 130
so
you can not update one row from other two row i think.
if you want, you shoud use aggerate function here :)

Damien

3/30/2007 10:34:00 AM

On Mar 30, 10:45 am, "simonZ" <simon.zu...@studio-moderna.com> wrote:
> Thanx.
>
> I know I can use agregate but
> if select join statement returns 3 rows, than also the same update from the
> same join should affect all 3 rows.
Should affect "all 3 rows" of the _2_ row table2? Interesting
concept...

If you get someone like CELKO in here, he'll point to this as a
perfect example of why the UPDATE...FROM (proprietry) syntax is so
dangerous.

Just go with:
UPDATE table2 set quantity = quantity + (select SUM(quantity) from
table1 t where t.warehouseID = table2.warehouseID and t.productID =
table2.productID)
WHERE exists (select * from table1 t where
t.warehouseID = table2.warehouseID and t.productID = table2.productID)

Strangely, I can't bend my mind around to formulating it in the
UPDATE...FROM syntax, even though that usually performs better than
the standards based one above.
>
> This is very tricky and can cause a lot of mistakes over sql users, because
> a lot of them doesn't know that and it's not so logical.
> I think that this should be repaired with some service pack :)

Highly unlikely. They *might* fix it in future by issuing a
cardinality warning if the same base table rows appear more than once
in the constructed row set, but that's about it (and would introduce a
probably unacceptable overhead to the update statement, forcing an
implicit GROUP BY to occur, which wouldn't be necessary for those
users who are careful enough to construct well formed UPDATE...FROM
statements).

Damien

Roy Harvey

3/30/2007 10:56:00 AM

Comments are in-line.

On Fri, 30 Mar 2007 10:40:31 +0200, "simonZ"
<simon.zupan@studio-moderna.com> wrote:

>I have very simple example.
>
>If I write select statement:
>
>select c.* from table1 c INNER JOIN table2 i
>ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
>I get 3 rows:
>
>warehouseID productID quantity
>-----------------------------------------
> 1 'A' 10
> 1 'A' 20
> 2 'B' 30

But note that the two columns of the JOIN only have two different sets
of values. The first two rows match to only one row in table i, and
it is table i that you will be updating.

>I I write UPDATE statement:
>
>UPDATE i SET i.quantity=i.quantity+c.quantity
>from table1 c INNER JOIN table2 i
>ON c.warehouseID=i.warehouseID AND c.productID=i.productID
>
>only 2 rows are updated.
>Product A is updated only for quantity=10, not for quantity=20.

Correct. There are only two rows in table i that match rows in table
c, so only two rows will be updated.

THE USE OF A FROM CLAUSE IN AN UPDATE COMMAND IS NOT STANDARD SQL!! It
is a Transact-SQL extension. The reason this configuration, the
configuration you used, is not part of the standard is because it can
produce logical contradictions such as the situation you find yourself
in here. As you say, only two rows are being updated, but the first
row is being updated TWICE. But, BOTH times it is the ORIGNAL version
of the row that is being updated, and it is unpredictable which of the
two changes happens last and is the final version.

Now, the use of FROM in UPDATE is a powerful feature despite the sort
of problems you are having, but it does require some care. Here is
how you can make it work for your situation.

UPDATE Table2
SET Table2.quantity = Table2.quantity + X.SummedQty
FROM (SELECT C.warehouseID, C.productID,
SUM(C.quantity) as SummedQty
FROM table1 as C
GROUP BY C.warehouseID, C.productID) as X
WHERE C.warehouseID = X.warehouseID
AND C.productID = X.productID

The key change is that in place of table1 there is a Derived Table
that aggregates the data from table1 so that there is a one-to-one
relationship between the rows in Table2 being updated and the data
being used for the update. The other change was to remove the
reference to Table2 from the FROM clause, and move the "join" to the
WHERE clause of the outer query. This is optional, but it is the
arrangement I prefer. It requires using the table name, not an alias,
which helps make it clear which table is being updated. It also
avoids the problem of an alias being assigned but some references
getting the table name.

>Why update statement doesn't effect as many rows as select statement
>returns?
>
>Regards,Simon

Roy Harvey
Beacon Falls, CT

bob

3/30/2007 1:08:00 PM

Check it like this:

DROP TABLE #tmp1
DROP TABLE #tmp2
GO

CREATE TABLE #tmp1 ( test_id INT PRIMARY KEY )
CREATE TABLE #tmp2 ( test_id INT NOT NULL )
GO

SET NOCOUNT ON

INSERT INTO #tmp1 VALUES( 1 )
INSERT INTO #tmp1 VALUES( 2 )
INSERT INTO #tmp2 VALUES( 2 )
INSERT INTO #tmp2 VALUES( 2 )
GO

SET NOCOUNT OFF

-- Reproduce the problem
SELECT *
FROM #tmp1 t1
INNER JOIN #tmp2 t2 ON t1.test_id = t2.test_id
GO
UPDATE t1
SET t1.test_id = t2.test_id
FROM #tmp1 t1
INNER JOIN #tmp2 t2 ON t1.test_id = t2.test_id
GO

-- Update with cardinality check
DECLARE @actual INT,
@expected INT

-- Get the number of rows expected
SELECT @expected = COUNT(*)
FROM #tmp1 t1
INNER JOIN #tmp2 t2 ON t1.test_id = t2.test_id

UPDATE t1
SET t1.test_id = t2.test_id
FROM #tmp1 t1
INNER JOIN #tmp2 t2 ON t1.test_id = t2.test_id

-- Capture no. of rows affected
SET @actual = @@rowcount

IF @actual <> @expected
RAISERROR( 'Wrong number of rows affected. Actual(%i) Expected(%i).', 16,
1, @actual, @expected )

wBob

"simonZ" wrote:

> Thanx.
>
> I know I can use agregate but
> if select join statement returns 3 rows, than also the same update from the
> same join should affect all 3 rows.
>
> This is very tricky and can cause a lot of mistakes over sql users, because
> a lot of them doesn't know that and it's not so logical.
> I think that this should be repaired with some service pack :)
>
> regards,Simon
>
>
> "xyb" <xiangyuanbo@gmail.com> wrote in message
> news:1175246779.324079.169690@e65g2000hsc.googlegroups.com...
> On 3Ã?Ã?30Ã?Ã?, ÃÃ?Ã?Ã§4Ã?Â±56Â·Ã?, "simonZ" <simon.zu...@studio-moderna.com> wrote:
> > Here is DDL:
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[table1]
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[table2]
> >
> > CREATE TABLE [dbo].[table1] (
> > [rowID] [int] IDENTITY (1, 1) NOT NULL ,
> > [warehouseID] [int] NOT NULL ,
> > [productID] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
> > [quantity] [int] NULL
> > ) ON [PRIMARY]
> >
> > CREATE TABLE [dbo].[table2] (
> > [warehouseID] [int] NOT NULL ,
> > [productID] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
> > [quantity] [int] NOT NULL
> > ) ON [PRIMARY]
> >
> > INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'A',10)
> > INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'A',20)
> > INSERT INTO table1(warehouseID,productID,quantity) VALUES (1,'B',30)
> >
> > INSERT INTO table2(warehouseID,productID,quantity) VALUES (1,'A',5)
> > INSERT INTO table2(warehouseID,productID,quantity) VALUES (1,'B',100)
> >
> > begin tran
> > select * from table2
> >
> > select c.* from table1 c INNER JOIN table2 i
> > ON c.warehouseID=i.warehouseID AND c.productID=i.productID
> >
> > UPDATE i SET i.quantity=i.quantity+c.quantity
> > from table1 c INNER JOIN table2 i
> > ON c.warehouseID=i.warehouseID AND c.productID=i.productID
> >
> > select * from table2
> > rollback tran
> >
> > Quantity in table2 should be 35, but it's not.
> >
> > Regards,S
> >
> > "simonZ" <simon.zu...@studio-moderna.com> wrote in message
> >
> > news:O6j5wbqcHHA.1240@TK2MSFTNGP04.phx.gbl...
> >
> >
> >
> > >I have very simple example.
> >
> > > If I write select statement:
> >
> > > select c.* from table1 c INNER JOIN table2 i
> > > ON c.warehouseID=i.warehouseID AND c.productID=i.productID
> >
> > > I get 3 rows:
> >
> > > warehouseID productID quantity
> > > -----------------------------------------
> > > 1 'A' 10
> > > 1 'A' 20
> > > 2 'B' 30
> >
> > > I I write UPDATE statement:
> >
> > > UPDATE i SET i.quantity=i.quantity+c.quantity
> > > from table1 c INNER JOIN table2 i
> > > ON c.warehouseID=i.warehouseID AND c.productID=i.productID
> >
> > > only 2 rows are updated.
> > > Product A is updated only for quantity=10, not for quantity=20.
> >
> > > Why update statement doesn't effect as many rows as select statement
> > > returns?
> >
> > > Regards,Simon- Ã?Ã¾Â²Ã?Â±Â»Ã?Ã½Ã?Ã?Ã?Ã?Ã?Ã? -
> >
> > - ÃÃ?Ã?Â¾Ã?Ã½Ã?Ã?ÂµÃ?Ã?Ã?Ã?Ã? -
>
> thank you for post DDL
> you see that table2 have only two row,and UPDATE clause based on this
> 2 rows to update,
> so if i have not misunderstood your meaning,you will want data as:
> 1 A 35
> 1 B 130
> so
> you can not update one row from other two row i think.
> if you want, you shoud use aggerate function here :)
>
>
>

--CELKO--

3/31/2007 12:41:00 AM

>> If you get someone like CELKO in here, he'll point to this as a
perfect example of why the UPDATE...FROM (proprietry) syntax is so
dangerous. <<

I would like to point that this is a perfect example of why the
UPDATE...FROM (proprietry) syntax is so dangerous :)

microsoft.public.sqlserver.programming

SELECT and UPDATE

simonZ

simonZ

Rahul

xyb

xyb

simonZ

Damien

Roy Harvey

bob

--CELKO--

x Login to ForumsZone