0

I have a table where I want to update a column with the value in another column in the previous row.

I want to do something like this.

UPDATE myTable as b SET prev=(SELECT top 1 myField FROM myTable 
WHERE rowID<b.rowID ORDER By rowID Desc)

Any ideas?

Note: I'm using Access/SQL server. I am getting an error 'operation must use an updatable query'.

Note2: The solution from How do I UPDATE from a SELECT in SQL Server? doesnt work.

Community
  • 1
  • 1
user3338308
  • 1
  • 1

2 Answers2

0

You can achieve this with a combination of the LAG() analytic function to compute the value of for the previous row and an UPDATE (here, I'm using a MERGE statement):

MERGE INTO mytable as target using (
  select 
    t.id,
    lag(field1) over (partition by null order by id) new_prev_field
  from mytable t
) as src (id, new_prev_field)
on (target.id = src.id)
when matched 
then update
  set prev_field1 = src.new_prev_field;

SQL Fiddle

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
-1

Instead of Top, use Max()

UPDATE myTable as b 
SET prev = (
  SELECT max(rowID)
  FROM myTable 
  WHERE rowID < b.rowID)
toddsonofodin
  • 513
  • 2
  • 4
  • That doesn't do what the OP wants - he wants some field_value from the previous row, not the rowID of the previous row. – Frank Schmitt Feb 21 '14 at 18:37
  • True, I interpreted it incorrectly. In my defense best practice would be to store the ID, as then you could join back to the table and get whatever dimension/attribute needed rather than physically store one. – toddsonofodin Feb 21 '14 at 19:01