Update a table

• lalit-450914

  SSC-Addicted

  Points: 432

  More actions

  October 22, 2007 at 10:00 pm

  #183072

  Hi,

  I have two set of similar rows, one with type = 1 and other with type = 2

  Create table #CustomersEng (custid int,name nvarchar(20),age int, st_code int,

  citycode int, internalid int,type int,oldcustid int)

  type =1

  Insert into #CustomersEng values(101,'Aron',23,1,1,12,1,0);

  Insert into #CustomersEng values(102,'Cathy',28,1,1,13,1,0);

  Insert into #CustomersEng values(103,'Zarog',33,1,1,14,1,0);

  Insert into #CustomersEng values(104,'Michale',25,1,2,12,1,0);

  Insert into #CustomersEng values(105,'Linda',43,1,2,13,1,0);

  Insert into #CustomersEng values(106,'Burt',53,1,2,14,1,0);

  type = 2

  Insert into #CustomersEng values(107,'Aron',23,1,1,12,2,0);

  Insert into #CustomersEng values(108,'Cathy',28,1,1,13,2,0);

  Insert into #CustomersEng values(109,'Zarog',33,1,1,14,3,0);

  Insert into #CustomersEng values(110,'Michale',25,1,2,12,4,0);

  Insert into #CustomersEng values(111,'Linda',43,1,2,13,5,0);

  Insert into #CustomersEng values(112,'Burt',53,1,2,14,6,0);

  I want to update the oldcustid of rows with type = 2 with the custid of rows with type = 1 where the st_code, citycode and internalid are the same for both the rows.

  SAMPLE OUTPUT

  Insert into #CustomersEng values(107,'Aron',23,1,1,12,2,101);

  Insert into #CustomersEng values(108,'Cathy',28,1,1,13,2,102); and so on..

  How to do this?

• VaiydeyanathanVS

  Hall of Fame

  Points: 3053

  More actions

  October 22, 2007 at 11:01 pm

  #744316

  please use identity insert off/on and try to update the respective types accordingly.

• lalit-450914

  SSC-Addicted

  Points: 432

  More actions

  October 23, 2007 at 12:24 am

  #744333

  never mind..resolved it