update one table based on another table

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 2005 Forums

Transact-SQL (2005)

update one table based on another table

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2010-02-04 : 12:08:44

Here is code
select giftid,attrtype,
sum(case when gifttype in ('b','g','y') then giftjntamt else 0 end)as Gifts,
sum(case when gifttype = 'c' then giftjntamt else 0 end)as Credits
from gifts with(readpast)
left outer join attribute_VIEW with(readpast)
on attrid=giftid
where gifttype in ('b','c','g','y')
and gifteffdat >='2009-07-01'

group by giftid,attrtype
having sum(giftjntamt)>=250
sample data
giftid attrtype gifts credits
11111 Null 250 0
11211 SON24 355 122
12544 Null 125 125

I need to update all records in this query to populate SON24 in the attrtype field because all records have giving or credits of 250 or more. The field that needs updating is the attribute_VIEW field. The qualifying field is the gift field. So I need to update the attribute_VIEW table with SON24. Does the update statement go before or after the above query and how would I update when there are multiple tables involved

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 12:15:16

do you mean this?


UPDATE a
SET a.attrtype  = 'SON24'
FROM attribute_VIEW a
JOIN (select giftid,attrtype,
sum(case when gifttype in ('b','g','y') then giftjntamt else 0 end)as Gifts,
sum(case when gifttype = 'c' then giftjntamt else 0 end)as Credits 
from gifts with(readpast)
left outer join attribute_VIEW with(readpast)
on attrid=giftid
where gifttype in ('b','c','g','y')
and gifteffdat >='2009-07-01'

group by giftid,attrtype
having sum(giftjntamt)>=250)
) b
on b.giftid=a.giftid
where a.attrtype <> 'SON24'

make sure you run this in a transaction to see if result is desired and if not rollback

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources