CODEWITHSUNDEEP
sqlsql-server
Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34152

shifting some columns one record back or forward

I have a table with about 8000 rows and 15 columns. After I have inserted the data I saw that my data was wrong after a number of records (let's say 1000) some column values belong to the previous record some thing like this:

A           B              C (A+B)
==================================
1           1                  2
2           2                  4
3           3                  6
4           4                  8
5           5                  
6           6                  10
7           7                  12
8           8                  14
9           9                  16

Now I have to either move some column values a record back or forward and I don't actually have much option testing it I'm afraid I may overwrite some data and ruin the whole table

I should do something like this but for about 7000 records:

update table1 
set B = (select B from table1 where id = 1000) 
where id = 999

Any ideas?

Upvotes: 0

Views: 46

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you know the ids are sequential with no gaps, you can use a join to look up the value you want:

update t1
    set c = tt1.c
    from table1 t1 join
         table1 t2
         on t1.id = t2.id - 1
    where t1.id > 1000;

If you cannot trust the ids, you can create the appropriate sequential number without gaps using row_number():

with toupdate as (
      select t.*, row_number() over (order by id) as seqnum
      from table1
     )
update t1
    set c = tt1.c
    from toupdate t1 join
         toupdate t2
         on t1.seqnum = t2.seqnum - 1
    where t1.id > 1000;

Upvotes: 2

MWillemse
MWillemse

Reputation: 980

First, you should always test your statements before making definate changes to your data. You could start a transaction and only commit when certain it went well or make a copy of your table (select * into x from y) and test on that.

To answer your question, try something like this;

WITH dataToUpdate AS(
    SELECT RowNr ,
           DATA,
           DataFromPreviousRow = FIRST_VALUE(data) OVER (ORDER BY RowNr ROWS 1 PRECEDING)
    FROM dbo.test
)
UPDATE dataToUpdate
SET data = dataToUpdate.DataFromPreviousRow;

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20794

Create another table with the same fields as the table in question. Insert the bad records. Fix the data in the new table. Update the real table from the new one.

Upvotes: 0

Related Questions