CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Vengat
Vengat

Reputation: 235

Update column in SQL Server 2008

In the following table,i want to update the BreakTime based on comparing the difference betweeen the next record time and current record time.

enter image description here

I have tried this query: 

update Machine_Data 
set BreakTime = (select DATEDIFF(SECOND, M2.Time, M1.Time) as BreakTime 
                 from Machine_Data M1 
                 join Machine_Data M2 on M1.DocEntry = M2.DocEntry + 1)

but it's throws an error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 2

Views: 48

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Since you have row numbers in your table, you can self join during the UPDATE to line up the times appropriately. Note that the join condition is that the current DocEntry should match to the one ahead of it in the joining table.

UPDATE t1
SET BreakTime = DATEDIFF(SECOND, t1.Time, COALESCE(t2.Time, t1.Time))
FROM Machine_Data t1
INNER JOIN Machine_Data t2
    ON t1.DocEntry = t2.DocEntry - 1

One other note is that the call to COALESECE() handles the edge case of the last record in the table (as sorted ascending by DocEntry). In this case, it won't match to any record, so I choose to report zero for the break time in this case.

If your BreakTime column is Time type, then you could just use DATEADD() to set the time:

SET BreakTime = DATEADD(SECOND,
                        DATEDIFF(SECOND, t1.Time, COALESCE(t2.Time, t1.Time)),
                        '00:00:00')

Upvotes: 3

vinay koul
vinay koul

Reputation: 346

update Machine_Data
set BreakTime = (select MAX(DATEDIFF(SECOND,M2.Time,M1.Time)) as BreakTime
                 from Machine_Data M1 join Machine_Data M2 on M1.DocEntry =M2.DocEntry+1)

Upvotes: 0

Related Questions