0

**I have been automatically collecting machine breakdown info to a database for 12 months, so I have thousands of entries in a 'Machine Downtime' table.

However, I have found that the time_down column is offset by one row. The 'time_down' in the top row needs to be moved to the second row, time_down in the second row moved to the third etc...

Timestamp | Reason | time_down
2012-05-10 10:15:14 | Line Jam | 00:10:00
2012-05-10 10:16:22 | Lunch Break | 00:02:44

There are no other columns in the table, the index is 'timestamp'

Is there an update query that I can use to move all rows automatically?

2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by adam_k
0

There is a way, but it's not just an update query.
I don't have a server available, so this might contain errors, and you didn't give a table name so I'm using MyTable.

select ROW_NUMBER() over (order by Timestamp) as 'row' , *  into MyTable_fix from MyTable

update a 
set a.time_down = b.time_down 
from MyTable_fix a inner join MyTable_fix b 
on a.row = b.row + 1 

delete from MyTable 

insert into MyTable 
select Timestamp, Reason, time_down from MyTable_fix 

drop table MyTable_fix 

Basically your table doesn't have a key that we can use, because it we use Timestamp we'll have to join like
a.Timestamp > b.Timestamp which will return a cartesian product. So we are creating one that we can control better.

Before you delete your records and insert the new ones in, you should take your time and verify that they are correct.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.