Help with 1toMany update/case stmt?

Hi all,
Looking for a way to do this in the minimum # of stmts I can.
Basically, this is doing a table1(1) to table2(many) update join. When
the t2 row has myindex = 0, then update column t1.c1.
The problem is that for each row in t2, it's index only matches 1 column
out of the 5 (c1..c5) in t1 for a given tuple. The other 4 columns
should not be updated in that case.
I tried to accomplish this my defaulting back to the orignal t1.cx value,
but I find that it is putting the ORIGINAL pre-update stmt value in
there, rather than the last value it was updated to during the update
operation.
Is there an option, or a way in a case stmt to say 'in this case, leave
the value alone and don't change it?'
Thanks!
UPDATE table1
SET c1 = CASE WHEN t2.myindex = 0 THEN t2.newvalue ELSE t1.c1 END,
c2 = CASE WHEN t2.myindex = 1 THEN t2.newvalue ELSE t1.c2 END,
c3 = CASE WHEN t2.myindex = 2 THEN t2.newvalue ELSE t1.c3 END,
c4 = CASE WHEN t2.myindex = 3 THEN t2.newvalue ELSE t1.c4 END,
c5 = CASE WHEN t2.myindex = 4 THEN t2.newvalue ELSE t1.c5 END
FROM table1 t1 FULL JOIN table2 t2 ON
( t1.mykey = t2.mykey
AND t1.mydate >= t2.fromdate
AND (t1.mydate <= t2.thrudate OR t2.thrudate IS NULL)
)
WHERE t1.status = 'x'
AND t2.status = 'y'you need to aggregate the values in table2 before you do the update. becaus
e
you're replacing the values, you need to essentially select the correct valu
e
in table2 before updating the row in table1. make it a 1=1 relationship
before doing the update. The problem with your current design is that there
is no definitive way to guarantee the order of the updates, so each time the
query runs, you could get different results.
In a side note, the predicate of your update statement doesn't make sense.
Why are you using a full join in an update statement? I'm not even sure what
happens to the rows in which the table1 columns are null. Are they ignored?
"xnews user" wrote:

>
> Hi all,
> Looking for a way to do this in the minimum # of stmts I can.
> Basically, this is doing a table1(1) to table2(many) update join. When
> the t2 row has myindex = 0, then update column t1.c1.
> The problem is that for each row in t2, it's index only matches 1 column
> out of the 5 (c1..c5) in t1 for a given tuple. The other 4 columns
> should not be updated in that case.
> I tried to accomplish this my defaulting back to the orignal t1.cx value,
> but I find that it is putting the ORIGINAL pre-update stmt value in
> there, rather than the last value it was updated to during the update
> operation.
> Is there an option, or a way in a case stmt to say 'in this case, leave
> the value alone and don't change it?'
> Thanks!
>
> UPDATE table1
> SET c1 = CASE WHEN t2.myindex = 0 THEN t2.newvalue ELSE t1.c1 END,
> c2 = CASE WHEN t2.myindex = 1 THEN t2.newvalue ELSE t1.c2 END,
> c3 = CASE WHEN t2.myindex = 2 THEN t2.newvalue ELSE t1.c3 END,
> c4 = CASE WHEN t2.myindex = 3 THEN t2.newvalue ELSE t1.c4 END,
> c5 = CASE WHEN t2.myindex = 4 THEN t2.newvalue ELSE t1.c5 END
> FROM table1 t1 FULL JOIN table2 t2 ON
> ( t1.mykey = t2.mykey
> AND t1.mydate >= t2.fromdate
> AND (t1.mydate <= t2.thrudate OR t2.thrudate IS NULL)
> )
> WHERE t1.status = 'x'
> AND t2.status = 'y'
>