Why doesn't this CASE work? 1

[bperry]

I am staring at an Update statement, with CASE conditions on two columns, and I cannot understand why it does not work properly.

(This is actually an expansion of thread183-266358)

We are updating TableA from TableB, as follows:
[tt]
TableB data looks like:
id type dollars
1 type1 25
1 type2 30
2 type2 50
2 type1 7

TableA before update looks like:
id type1_dollars type2_dollars
1 0 0
2 0 0

TableA after update should be:
1 25 30
2 7 50

BUT TableA after update actually is:
1 0 30
2 0 50
[/tt]
--------------------------------------

As you can see, the type1_dollars is not being updated. But type2_dollars are.

Here is my Update statement
[tt]
Update a SET
a.type1_dollars =
(CASE b.type when 'type1' then b.dollars
else a.type1_dollars END),
a.type2_dollars =
(CASE b.type when 'type2' then b.dollars
else a.type2_dollars END)

From tablea a INNER JOIN tableb b
ON a.id = b.id
WHERE b.type IN ('type1', 'type2')
[/tt]
-----------------------------------
If you are interested, here's some script to create the tables.

CREATE TABLE TableA (
id int NOT NULL,
type1_dollars int,
type2_dollars int,
Primary Key (id)
)
INSERT TableA Values (1,0,0)
INSERT TableA Values (2,0,0)

CREATE TABLE TableB (
id int NOT NULL,
type varchar(5),
dollars int,
Primary Key (id, type)
)
INSERT TableB VALUES (1,'type1',25)
INSERT TableB VALUES (1,'type2',30)
INSERT TableB VALUES (2,'type1',7)
INSERT TableB VALUES (2,'type2',50)
------------------------------------

 

[tlbroadbent]

The reason the update doesn't work as you expect is that SQL deals with sets of data that exist at the begining of the update, not intermediate values. When the tables are joined, four rows are returned with the following values.
[tt]
ID type type1_dols new_type1 type2_dols new_type2
-- ----- ---------- --------- ---------- ---------
1 type1 0 25 0 0
1 type2 0 0 0 30
2 type1 0 7 0 0
2 type2 0 0 0 50[/tt]

Each row in TableA is updated twice. Each time type1_dollars and type2_dollars are updated. It appears that the first update, modifies type1 correctly and type2 is updated tol the current value. The next update updates type2 correctly but resets type1 to the original value.

I recommend separate updates. However, just for fun, I created the following to update in one pass. It is rather cumbersome - much more so than a two pass query would be.

Update a SET
a.type1_dollars=b.type1_dollars,
a.type2_dollars=b.type2_dollars

FROM tablea a
INNER JOIN
(Select c.ID, c.type1_dollars, d.type2_dollars FROM
(Select Id, type1_dollars=dollars
From tableb Where type='type1') c
Inner Join
(Select Id, type2_dollars=dollars
From tableb Where type='type2') d
On c.id=d.id
) b
ON a.id = b.id Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

The next update updates type2 correctly
but resets type1 to the original value

Yes, of course, thanx. Hit me with a hammer.


The solution proposed by arrowhouse also works well:
Update a set
a.type1_dollars = b.dollars,
a.type2_dollars = c.dollars

from tablea a, tableb b, tableb c

where a.id = b.id and b.type = 'type1'
and a.id = c.id and c.type = 'type2'

---------------------
thanx again, tb