Update With Case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update With Case

Author

Topic

AndySavage
Starting Member

1 Post

Posted - 2002-12-20 : 23:40:32

Hi this is fairly frustrating, and I hope its just a small oversight I've made, but still. Here goes..

Im trying to update a table based on another table, fairly trivial.

A quick select statement will show what exactly im looking at in the table...

SELECT *
FROM tblObjects
INNER JOIN tblBuild ON tblObjects.UserID = tblBuild.UserID
WHERE ETA = 1

This returns 2 rows.

(ID / Type / Number / ETA.. plus some more I dont care about)

00001 / 1 / 5 / 1
00002 / 0 / 5 / 1

I wish to increase different fields in tblObjects by the field Number, based on the field type.

UPDATE tblObjects SET
AnObject = AnObject +
CASE
WHEN Type = 0 THEN Number
ELSE 0
END,
DiffObject = DiffObject +
CASE
WHEN Type = 1 THEN Number
ELSE 0
END
FROM tblObjects
INNER JOIN tblBuild
ON tblObjects.UserID = tblBuild.UserID
WHERE ETA = 1

However the update does not perform as I expected. Even though 2 rows are returned by the SELECT above, only 1 row is updated when exectuing the update and I cant understand why.

The CASE's trap the types correctly, its only when there are 2 differing types with the same ETA, that only one gets updated.

A little longwinded, I hope this is clear enough.

Any suggesttions would be appreciated.

Cheers, Andy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-21 : 11:35:44

CREATE TABLE #tblObjects(
UserID nvarchar(5),
AnObject int,
DiffObject int
)

CREATE TABLE #tblBuild(
UserID nvarchar(5),
Type int,
Number int,
ETA int,
)

INSERT INTO #tblObjects (UserID, AnObject,DiffObject) VALUES (00001,0,1)
INSERT INTO #tblObjects (UserID, AnObject,DiffObject) VALUES (00002,1,2)
INSERT INTO #tblObjects (UserID, AnObject,DiffObject) VALUES (00003,2,3)

INSERT INTO #tblBuild (UserID, Type, Number, ETA) VALUES (00001,1,5,1)
INSERT INTO #tblBuild (UserID, Type, Number, ETA) VALUES (00002,0,5,1)
INSERT INTO #tblBuild (UserID, Type, Number, ETA) VALUES (00003,1,5,2)

SELECT *
FROM #tblObjects
INNER JOIN #tblBuild ON #tblObjects.UserID = #tblBuild.UserID
WHERE ETA = 1

UPDATE #tblObjects SET
AnObject = A.AnObject +
CASE
WHEN B.Type = 0 THEN B.Number
ELSE 0
END,
DiffObject = A.DiffObject +
CASE
WHEN B.Type = 1 THEN B.Number
ELSE 0
END
FROM #tblObjects A
INNER JOIN #tblBuild B
ON A.UserID = B.UserID
WHERE B.ETA = 1

SELECT *
FROM #tblObjects
INNER JOIN #tblBuild ON #tblObjects.UserID = #tblBuild.UserID
WHERE ETA = 1

DROP TABLE #tblObjects
DROP TABLE #tblBuild



UserID AnObject  DiffObject  UserID Type    Number   ETA         
------ --------  ---------- ------ -------  ------- -------- 
1        0           1         1      1        5      1
2        1           2         2      0        5      1


UserID AnObject    DiffObject  UserID Type      Number  ETA         
------ ---------  ----------- ------ ------    ----- --------- 
1        0            6         1      1         5      1
2        6            2         2      0         5      1

Both AnObject and DiffObject were updated.
By the way I'm using SQL2k

Edited by - ValterBorges on 12/21/2002 11:36:43

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources