UPDATE the same row multiple times in one query
-
Is it possible to update the same row multiple times in one query and remain sensitive to it's existing value?
UPDATE t1
SET
t1.value = CASE
WHEN t1.value IS NULL OR t2.value < t1.value THEN t2.value
ELSE t1.value END,
t1.status = CASE
WHEN t1.status IS NULL OR t1.status=t2.status THEN t2.status
ELSE 'X' END
FROM table1 t1
JOIN table2 t2 ON t2.key = t1.keyThe idea is that the first instance of the key record from table2 will simply set the values in table1, then subsequent instances of the key will update only under certain conditions.
Do I have to use a cursed cursor for this?
Let me know if I'm not being clear.
-
Can you post sample data and sample result.
Usually what you do is to compute how it is supposed to be and perform just one DML
* Noel -
Here's some sample data.
table1 before
key, value, status
1, null, null
2, null, null
3, null, null
4, null, null
table2
key, value, status
1, 5, 'A'
1, 2, 'A'
2, 6, 'U'
4, 3, 'A'
4, 9, 'U'
4, 7, 'A'
table1 after
key, value, status
1, 2, 'A'
2, 6, 'U'
3, null, null
4, 3, 'X'
TIA,
John
-
I haven't tested this but it should be pretty close to what you need:
Update t1
set value = t2.minval, status =t3.st
from table1 t1
join
(select key, min(value) minval
from table2
group by value) t2 on t2.key = t1.key
join ( select key, (case when count(*) = count(distict status) then min(status) else 'X' end) as st
from table2
group by key ) t3 on t3.key = t1.key
* Noel -
OK this is the revised (and checked) version
Update table1
set value = t2.minval, status =t3.st
from table1 t1
left join
(select , min(value) minval
from table2
group by ) t2 on t2. = t1.
left join ( select , (case when count(distinct status) = 1 then min(status) else 'X' end) as st
from table2
group by ) t3 on t3. = t1.
hth
* Noel -
Thanks for your help, Joe.
I am an application developer by trade and through necessity became a self taught mssql developer - I know, a dangerous combo. Anyway, my description of _the idea_ was assuming a cursor implementation. Sorry for using that terminology while asking for a set solution.
I was just putting together a DDL script to post when I saw your second post and as it has answered my question rather elegantly, I'll skip it, thank you and my lucky stars and be more careful next time.
Noel, thanks for your help too.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply