CODEWITHSUNDEEP
sqlsql-server-2008
D Jaison
D Jaison

Reputation: 53

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

Upvotes: 1

Views: 113

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

seems you have wrong sintax (update don't have from clause and the table/join must be indicated before set )

Update Table-1
 inner Join Table-2 on Table-1.TynameName=Table-2.Typename 
        and Table1.Ordersequence>=table-2.ordersequence
SET Table-1.Time= DATEADD(minutes,,table-2.delta,table-1.time)
where table-2.ordersequence != 9
;

Upvotes: 0

Related Questions