CODEWITHSUNDEEP
t-sql
user219628
user219628

Reputation: 3905

T-SQL update record from other row number

In SQL Server 2008, I have table1 and would like to update table as shown in second table. i.e. Update update Value1 of Rn(2) = Value2 of Rn(1) from above record. Sequence is decided by Rn. Any help in this regard will be appreciated.

alt text

Many Thanks.

Create table Table1(ID int, value1 int, value2 int, Item varchar(10),Rn int)

insert into Table1(ID,Value2,Item,Rn) values('1','33242','Orange','1')
insert into Table1(ID,Value2,Item,Rn) values('2','67665','Orange','2')
insert into Table1(ID,Value2,Item,Rn) values('3','78765','Orange','3')
insert into Table1(ID,Value2,Item,Rn) values('4','576','Orange','4')
insert into Table1(ID,Value2,Item,Rn) values('5','906658','Orange','5')
insert into Table1(ID,Value2,Item,Rn) values('6','54435','Orange','6')
insert into Table1(ID,Value2,Item,Rn) values('7','7464','Mango','1')
insert into Table1(ID,Value2,Item,Rn) values('8','9876','Mango','2')
insert into Table1(ID,Value2,Item,Rn) values('9','2433','Mango','3')
insert into Table1(ID,Value2,Item,Rn) values('10','5654','Mango','4')
insert into Table1(ID,Value2,Item,Rn) values('11','13213','Mango','5')
insert into Table1(ID,Value2,Item,Rn) values('12','9867867','Mango','6')
insert into Table1(ID,Value2,Item,Rn) values('13','5644355','Mango','7')
insert into Table1(ID,Value2,Item,Rn) values('14','6534','Apple','1')
insert into Table1(ID,Value2,Item,Rn) values('15','343','Apple','2')
insert into Table1(ID,Value2,Item,Rn) values('16','423','Apple','3')
insert into Table1(ID,Value2,Item,Rn) values('17','7666','Apple','4')

Upvotes: 0

Views: 1510

Answers (4)

Philip Kelley
Philip Kelley

Reputation: 40309

This sets Value1 of the first row of each "block" to null. You could use ISNULL to override this and make it a 0, or whatever other default value may be appropriate.

;WITH cte (Id, Value1, Value2, Item, Rn, RnPlus1)
 as (select Id, Value1, Value2, Item, Rn, row_number() over (partition by Item order by Rn) + 1 RnPlus1
 from Table1) 
UPDATE Table1
 set Value1 = cte.Value2
 from Table1 t1
  inner join cte
   on cte.Item = t1.Item
    and cte.RnPlus1 = t1.Rn

Upvotes: 2

Gerrat
Gerrat

Reputation: 29680

Off the top of my head, something like:

update table1 t1 set t1.value1 = (select t2.value2 from table1 t2 where t2.id = t1.id + 1) where t1.rn != 1

Upvotes: 0

Mike Mengell
Mike Mengell

Reputation: 2398

I've always found this to be a right pain in the bum. But I would suggest a cursor. Something like this; (This is just the skeleton. It doesn't work! But you should be able to see the principle)

DECLARE @field1 integer DECLARE @fiedl2 integer DECLARE @NewValue integer

SET @NewValue = 0

DECLARE Tab1 CURSOR FOR SELECT * FROM Table1

FETCH NEXT FROM Tab1 INTO @field1, @field2

WHILE @@FETCH_STATUS = 0 BEGIN

IF @NewValue <> 0
    UPDATE Table1 SET field1 = @NewValue

FETCH NEXT FROM Tab1
INTO @field1, @field2

SET @NewValue = @field2

END

CLOSE Tab1 DEALLOCATE Tab1

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85046

Might be difficult since you haven't defined any parent child relationships. Can you just do it at insert time (and possibly define the relationship then...)?

insert into Table1(ID,Value1,Value2,Item,Rn) values('1','','33242','Orange','1')
insert into Table1(ID,Value1,Value2,Item,Rn) values('2','33242','67665','Orange','2')
insert into Table1(ID,Value1,Value2,Item,Rn) values('3','67665','78765','Orange','3')
insert into Table1(ID,Value1,Value2,Item,Rn) values('4','78765','576','Orange','4')

and so on...

Upvotes: 0

Related Questions