Update a column of table when there is matched records of another table

• sunnyepgc

  Mr or Mrs. 500

  Points: 546

  More actions

  January 19, 2011 at 7:12 am

  #233068

  Hi,

  i have 2 tables emp_stage ,emp_dest ..I have some records in the emp_stage table.Now i want to insert thse records into the emp_dest table by satisfying the below mentioned conditions.

  1)If there are same records found in both the tables then status field of emp_dest table should be updated to 'D'

  2)If there are no matched records then that row should be inserte dinto the emp_dest table .

  table1:emp_stage

  empid ename dept status

  100 sunny 10 N

  100 sunny 10 N

  200 deepak 20 N

  then in my emp_dest table should be

  empid ename dept status

  100 sunny 10 D

  200 deepak 20 N

• CGSJohnson

  SSCertifiable

  Points: 7208

  More actions

  January 19, 2011 at 10:09 am

  #1275417

  Have a look in Books Online at the merge statement. It's what's called an upsert...update when present, otherwise insert. Hope that this helps.

  Thanks...Chris

• rvphx

  SSCrazy

  Points: 2120

  More actions

  January 19, 2011 at 12:45 pm

  #1275524

  If you dont have 2008 installed on your machine, then separate out the insert/update logic. But if you have 2008, Merge is the best option.

• @SQLFRNDZ

  SSCrazy Eights

  Points: 9095

  More actions

  January 19, 2011 at 3:49 pm

  #1275609

  Use update table statement.

  [font="Tahoma"]
  --SQLFRNDZ[/url]
  [/font]