October 11, 2009 at 8:24 pm
i have the following tables
declare @t1 table
(
a int,
b int,
c int
)
declare @t2 table
(
a int,
x int,
y int
)
and say it has the following data
insert into @t1 values (1,0,0)
insert into @t2 values (1,1,1)
insert into @t2 values (1,2,1)
i want to update t1 with the values from t2, but using the following logic
update t1
set
t1.b = (case when t2.x = 1 then t1.b + t2.y else t1.b end),
t1.c = (case when t2.x = 2 then t1.c + t2.y else t1.c end)
from @t1 t1 join @t2 t2 on t1.a = t2.a
unfortunately only the first row in t2 is being applied. i would like to know whether it is possible to have both rows in t2 applied to t1 using a single update statement.
i know that i can use a where on the update but wouldnt that be just as bad as looping through every row in t2 and updating t1 accordingly
tia
October 11, 2009 at 10:48 pm
txp001 (10/11/2009)
on t1.a = t2.a
The conditions matches the first row in the t2, so that it’s update/ compare the first row data,
Just assumption of update t1 table column-b with min value in the t2, and column-c with max value in the t2.
update t1
set t1.b = (case when t2.x = 1 then t1.b + t2.y else t1.b end),
t1.c = (case when t3.x = 2 then t1.c + t3.y else t1.c end)
from @t1 t1 join
(select a,min(x) x,min(y) y from @t2
group by a) as t2
on t1.a = t2.a
join
(select a,max(x) x,max(y) y from @t2
group by a) as t3
on t1.a = t3.a
October 12, 2009 at 6:14 pm
sorry, my bad. what i showed was a cut down version. t1 has more columns and is more akin to
declare @t1 table
(
a int,
jan int,
feb int,
[...]
dec int
)
and as i am sure you can guess, the value in t2.x determines which column is updated.
i am also not sure why you say that it only matches the first row in t2. when i do this
select
t1.*,
t2.*
from @t1 t1 join @t2 t2 on t1.a = t2.a
i get two rows. i was hoping that update would be applied to every row in the from clause even though they are the same one, but it appears to only do the first one.
anyway you have given me an idea. i can do something like this
update t1
set t1.b = (case when t2.y is null then t1.b else t1.b + t2.y end),
set t1.c = (case when t3.y is null then t1.b else t1.b + t3.y end)
from @t1 t1
left join (select a, sum(y) y from @t2 where x = 1 group by a) t2 on t2.a = t1.a
left join (select a, sum(y) y from @t2 where x = 2 group by a) t3 on t3.a = t1.a
now just have to figure out if it is any faster than processing it row by row.
thanks
October 13, 2009 at 2:45 am
i am also not sure why you say that it only matches the first row in t2. when i do this
selectt1.*,
t2.*
from @t1 t1 join @t2 t2 on t1.a = t2.a
i get two rows. i was hoping that update would be applied to every row in the from clause even though they are the same one, but it appears to only do the first one.
Interesting,(I think its very basic understanding which we are missing) I would wish if someone can throw some light on it.
If you look at the execution plan for this,
update t1
set t1.b = (case when t2.x = 1 then t1.b + t2.y else t1.b end),
t1.c = (case when t2.x = 2 then t1.c + t2.y else t1.c end) -- select t1.*, t2.*
from #t1 t1 join #t2 t2 on t1.a = t2.a
After inner join, we have a stream aggregate which is grouping by (bmk1000(bookmark?))and getting the top row of t1 for update.
Hence the second update is not working.
---------------------------------------------------------------------------------
October 13, 2009 at 6:31 am
You need to aggregate the data down before running the update. Given that your case statements are additive, then if you always have positive values in t2 you can use a nested CTE with an aggregate:
create table #t1 (
a int,
b int,
c int)
create table #t2 (
a int,
x int,
y int)
insert into #t1 values (1,0,0)
insert into #t2 values (1,1,1)
insert into #t2 values (1,2,1)
select * from #t1
-- abc
-- 100
go
;with cte as (
select
t1.a
, case when t2.x = 1 then t1.b + t2.y else t1.b end as b
, case when t2.x = 2 then t1.c + t2.y else t1.c end as c
from #t1 t1 join #t2 t2 on t1.a = t2.a
)
, cte2 as (
select a, max(b) as b_max, max(c) as c_max
from cte
group by a
)
update #t1
set b = cte2.b_max
, c = cte2.c_max
from #t1
join cte2
on #t1.a = cte2.a
go
select * from #t1
-- abc
-- 111
Not necessarily faster though...
October 13, 2009 at 7:28 am
This should be quicker:
;WITH cte
AS
(
SELECT a
,COALESCE(MAX(CASE WHEN x = 1 THEN y END), 0) AS b
,COALESCE(MAX(CASE WHEN x = 2 THEN y END), 0) AS c
FROM @t2
GROUP BY a
)
UPDATE T
SET b = T.b + C.b
,c = T.c + C.c
FROM @t1 T
JOIN cte C
ON T.a = C.a
This should be safer:
UPDATE T1
SET b = b +
COALESCE
(
(
SELECT y
FROM @t2 T2b
WHERE T2b.a = T1.a
AND T2b.x = 1
)
, 0
)
,c = C +
COALESCE
(
(
SELECT y
FROM @t2 T2c
WHERE T2c.a = T1.a
AND T2c.x = 2
)
, 0
)
FROM @t1 T1
WHERE EXISTS
(
SELECT *
FROM @t2 T2
WHERE T2.a = T1.a
)
October 13, 2009 at 8:27 pm
thanks all. the code was getting messy with all those joins as there were 12 of them, and way to many columns to join on too.
btw
Ken McKelvey (10/13/2009)
This should be quicker:
[...]
This should be safer:
[...]
why is one safer than the other
October 14, 2009 at 3:54 am
The following blog does a reasonable job of explaining the potential problems of JOINs in the UPDATE statement.
http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply