can you UPDATE with a self join

• ksexton

  SSCrazy

  Points: 2790

  More actions

  July 15, 2002 at 10:11 am

  #78539

  Is it possible to do an update statement using a self join? We are currenlty using a cursor.

  I have oversimplified the example:

  UPDATE table_x

  SET b.elig_end_Date = a.elig_end_Date+1

  FROM table_x a, table_x b

  WHERE a.elig_tag<>b.elig_tag

  Thanks!

  -K

• Antares686

  SSC Guru

  Points: 125444

  More actions

  July 15, 2002 at 11:19 am

  #432728

  Yes an update this way is quite common. If you want to test without affecting your data you can do

  SELECT * FROM table_x

  BEGIN TRANS

  YourUpdate Here

  SELECT * FROM table_x

  If it looks right then do a

  COMMIT TRANS

  or if an error

  ROLLBACK TRANS

  you will want to do this when not much is going on. Also test the select portion first before you try the test.

  On thing thou if WHERE a.elig_tag<>b.elig_tag won't if effect every record in a varifing manner, unless you have two records only.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)