Creating a SQL update query based on multiple columns and records in same table

Question

I have got a table which contains 5 column and query requirements:

update row no 8 (or id=8) set its column 2, column 3's value
            from id 9th column 2, column 3 value.

means all value of column 2, 3 should be shifted to column 2, 3 of upper row (start from row no 8) and value of last row's 2, 3 will be null

For example, with just 3 rows, the first row is untouched, the second to N-1^th rows are shifted once, and the N^th row has nulls.

 id  math  science sst hindi english
  1   11     12     13  14   15
  2   21     22     23  24   25
  3   31     32     33  34   35

The result of query of id=2 should be:

 id  math  science sst hindi english
  1   11     12     13  14   15
  2   31     32     23  24   25        //value of 3rd row (col 2,3) shifted to row 2
  3   null   null   33  34   35

This process should run for all rows whose id > 2

Please help me to create this update query

I am using MS sqlserver 2005

Answer 1

I THINK what you are looking for is something like...

UPDATE t1
   SET 
      t1.math = t2.math,
      t1.science = t2.science,
      etc...
   FROM 
      YourTable t1, 
      YourTable t2
   WHERE 
      t1.id+1 = t2.id

Notice the WHERE is for the first instance table's ID +1 being equal to the ID in the second instance. So if on table 1 ID = 8, it will join to second instance's ID = 9. At the end, if only 10 records, 10+1 would not have a match, and thus result in NULL.

Answer 2

How about:

--  @StartAt is the "first" (lowest Id) row to be updated
UPDATE MyTable
 set
   math = mt2.math
  ,science = mt2.sceience
 from MyTable mt
  left outer join MyTable mt2
   on mt2.Id = mt.Id + 1
 where mt.Id >= @StartAt

In your example, set @StartAt to 2. The "last" row gets set to nulls through the left outer join's finding no row to join to. (This presumes that all sequential rows are found. If you were missing a row, a set of NULLs will "sneak in" and overwrite some real data...)

Answer 3

You can do that with an Update and a Join.

UPDATE TempTable2
SET math=T2.Math,
science=T2.science,
sst=T2.sst,
hindi=T2.hindi,
english=T2.english
FROM TempTable2 T
    LEFT JOIN 
    (SELECT id -1 as ID, math, science, sst, hindi, english
    FROM temptable2 ) T2
    ON T.ID=T2.Id
WHERE T.id>2