February 20, 2009 at 8:44 am
Hello,
Is there a way to run a query and use the results from that to update another table without using a cursor?
Currently, I am using this code (which works fine - but in SQL 2000 it takes several seconds and 2005 takes over an hour - and as you might guess I'm migrating to 2005):
declare T_cursor cursor for
select C.ChildID, D.DATE_TERM
from Child C inner join DEMO D on C.CL_ID = D.CL_ID and C.Ofc_Code = D.FROMOFFICE
where C.DateInactivated is null and D.DATE_TERM is not null and D.STATUS >= '30'
open T_cursor
fetch next from T_cursor into @ChildID, @DATE_TERM
while (@@fetch_status=0)
begin
-- close out child record
update Child set
DateInactivated = @DATE_TERM,
UserChanged = 'Auto Close Out',
DateChanged = getdate()
where ChildID = @ChildID
-- close out child staff records
update ChildStaff set
DateEnd = @DATE_TERM,
UserChanged = 'Auto Close Out',
DateChanged = getdate()
where ChildID = @ChildID and DateEnd is null
fetch next from T_cursor into @ChildID, @DATE_TERM
end
close T_cursor
deallocate T_cursor
As you can see, I need to pull the DATE_TERM column and update based on what's contained therein.
Thanks for the help!
Heather
February 20, 2009 at 8:57 am
[font="Courier New"]BEGIN TRAN
UPDATE
C
SET
C.DateInactivated = D.DATE_TERM
, C.UserChanged = 'Auto Close Out'
, C.DateChanged = getdate()
from
Child C
inner join DEMO D on C.CL_ID = D.CL_ID and C.Ofc_Code = D.FROMOFFICE
where
C.DateInactivated is null
and D.DATE_TERM is not null
and D.STATUS >= '30'
UPDATE
CS
SET
CS.DateEnd
, CS.UserChanged = 'Auto Close Out'
, CS.DateChanged = getdate()
FROM
ChildStaff CS
INNER JOIN Child C ON C.ChildID = CS.ChildID
inner join DEMO D on C.CL_ID = D.CL_ID and C.Ofc_Code = D.FROMOFFICE
where
C.DateInactivated is null
and D.DATE_TERM is not null
and D.STATUS >= '30'
COMMIT TRAN[/font]
February 20, 2009 at 8:57 am
u can try this. it should be something like this.
update Child set
DateInactivated = a.DATE_TERM,
UserChanged = 'Auto Close Out',
DateChanged = getdate()
from Child c, (
select C.ChildID, D.DATE_TERM
from Child C inner join DEMO D on C.CL_ID = D.CL_ID and C.Ofc_Code = D.FROMOFFICE
where C.DateInactivated is null and D.DATE_TERM is not null and D.STATUS >= '30'
) a
where c.ChildID = a.ChildID
update ChildStaff set
DateEnd = a.DATE_TERM,
UserChanged = 'Auto Close Out',
DateChanged = getdate()
from ChildStaff c, (
select C.ChildID, D.DATE_TERM
from Child C inner join DEMO D on C.CL_ID = D.CL_ID and C.Ofc_Code = D.FROMOFFICE
where C.DateInactivated is null and D.DATE_TERM is not null and D.STATUS >= '30'
) a
where c.ChildID = a.ChildID
and c.DateEnd is null
February 20, 2009 at 9:02 am
Thanks! I will try both of these and the fastest method wins. 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply