Need help in nested query
-
I have 2 tables, relation is one to many:
- subscription (contactid, serviceid, lastsubscriptionstatus)
- subscriptionhistory (contactid, serviceid, dateofchange, subscriptionstatus)
A contact is subscribed to some service, and each time he unsubscribes or subsribes again, I create a new record in subscriptionhistory with subscriptionstatus=0 or 1 and the date, BUT I FORGOT TO UPDATE lastsubscriptionstatus
I need to pass a patch to update the subscription.lastsubscriptionstatus according to persons that are unsubscribed, saying that for each subscriptionhistory where subscriptionstatus=0, if it is the last subscriptionhistory for that contactid and serviceid, then update subscription.lastsubscriptionstatus.
update subscription
set lastsubscriptionstatus = 0 where ????
Am kind of stuck
-
Terry
I don't have access to a SQL Server 2005 server at the moment, so forgive me if the syntax isn't quite correct. Use a common table expression (CTE) something like this:
with hist as
(select contactid, serviceid, max(historydate)
from subscriptionhistory
where subscriptionstatus = 0
group by contactid, serviceid)
update s set lastsubscriptionstatus = 0
from subscription s join hist h
on h.contactid = s.contactid
and h.serviceid = s.serviceid
As always, test it before running it in production.
John
-
tx! i'll try although am not that strong in sql (dunno what CTE is
)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply