D Jaison D Jaison - 11 months ago 48
SQL Question

Inner join issue to update in SQL Server 2008

I have two tables and trying to update time with delta in table-1 using inner join but I am facing an issue with my below query.

UPDATE Table-1
SET Table-1.Time = DATEADD(minutes,,table-2.delta,table-1.time)
FROM Table-1
INNER JOIN Table-2 ON Table-1.TynameName = Table-2.Typename
AND Table1.Ordersequence >= table-2.ordersequence;

The time is getting updated for only ordernumber >4 (10-minutes) to order 9, and then delta updates for order 9 and greater for (5-minutes).

My result should be that order number greater than 4 should be update by (10-minutes) till 12 (order sequence) and greater than 9 should be updated by (5-minutes) till 12 (order sequence)

Right now I am using a while loop to resolve it by selecting each row of table-2 and updating table-1 time.

Is it possible to achieve it using joins?

enter image description here

Answer Source

Use cross apply:

Update Table-1
    set .Time= DATEADD(minutes, t2.delta, t1.time)
from [Table-1] t1 cross apply
      (select top 1 t2.*
       from [Table-2] t2
       where t1.Typename = t2.Typename and
             t1.Ordersequence >= t2.ordersequence
       order by t2.ordersequence desc
      ) t2;

The problem that you are having is that multiple rows match in Table-2. In that case, one of the rows is arbitrarily chosen for the update. The cross apply fixes this problem by choosing the first row that matches.