Can I do an insert and update in same query?
BEFORE:
MemberID | SubsID | StartDate | EndDate
------------------------------------------------
1001 | 10 | 2012-12-21 | 2012-12-31
2002 | 10 | 2012-12-22 |
AFTER:
MemberID | SubsID | StartDate | EndDate
------------------------------------------------
1001 | 10 | 2012-12-21 | 2012-12-31
2002 | 10 | 2012-12-22 | 2012-04-13
2002 | 10 | 2012-04-13 |
Get the rows:
select * from MemberSubs
where SubsID = 10 and EndDate is null;
Insert new rows:
insert into
MemberSubs(MemberID, SubsID, Price, StartDate)
select
MemberID, SubsID, Price, Current Date
from
MemberSubs
where
SubsID = 10
and
EndDate is null
Update the old rows:
update MemberSubs
set
EndDate = current date
where
SubsID = 10
and
EndDate is null
and
StartDate < Current Date
Is it possible to achieve this in one query (without using stored procedures or trigger etc)
Thank you.
