how to update based on query results without using cursor

• desotoroy

  SSC Enthusiast

  Points: 118

  More actions

  February 20, 2009 at 8:44 am

  #97343

  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

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  February 20, 2009 at 8:57 am

  #947581

  [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]

• Leo Leong

  SSCommitted

  Points: 1615

  More actions

  February 20, 2009 at 8:57 am

  #947582

  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

• desotoroy

  SSC Enthusiast

  Points: 118

  More actions

  February 20, 2009 at 9:02 am

  #947589

  Thanks! I will try both of these and the fastest method wins.