D Jaison D Jaison - 1 year ago 111
SQL Question

Inner join issue to update in sql server2008

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download