CODEWITHSUNDEEP
sqlsql-serversql-update
Eldho
Eldho

Reputation: 8273

Updating column in a table from another table using join

I have a two tables i need to copy from table 2 to table 1. I want the table 1. column departure to be updated on the matching in table 2.

Condition The table 2 have multiple items for the table 1,( table 1 have many relation with table 2). So in that case i need to sort it by RotationOrder and takes first row.

Table 1

Id  Code    Departure
479 JJ1256  NULL
480 SR1255  NULL
481 PFOBLEM NULL
482 SO1301  NULL
483 TS1302  NULL
484 YB1305  NULL
485 CU1303  NULL

Table 2 

Id  Departure   RotationOrder    CanLoad
479 NULL    1       1
480 NULL    1       2                 1
481 NULL    1       3                 1
482 NULL    1       4
482 NULL    3
482 NULL    2
482 NULL    4
483 2013-01-21  1                     1
483 NULL    3
483 NULL    4
483 NULL    6

What i have tried

UPDATE table1 set Departure = (select top 1 table2.Departure from table2 
INNER JOIN table1 on table1.Id = table2.Id  where CanLoad =1 order by 
RotationOrder )
FROM TABLE1  INNER JOIN TABLE2 
ON TABLE1.Id = TABLE2.Id

Problem This query copy first null value from the table2 and paste it on table1. which is incorrect.

Upvotes: 0

Views: 62

Answers (3)

George Menoutis
George Menoutis

Reputation: 7240

Cross apply top 1, beloved classic:

UPDATE t1 set Departure = q.Departure
FROM table1  t1
cross apply 
(
    SELECT TOP 1 Departure
    FROM table2
    WHERE t1.Id=table2.Id
    ORDER BY RotationOrder asc
)q

Upvotes: 2

Thom A
Thom A

Reputation: 95561

This is the way you'll want to do it:

UPDATE T1
SET Departure = T2.Departure
FROM Table1 T1
     CROSS APPLY (SELECT ca.*
                  FROM Table2 ca
                  WHERE T1.Id = ca.Id
                  ORDER BY ca.RotationOrder) T2;

Notice the use of the table's alias in the UPDATE clause. If you use the table's actual name (not it's alias) you are technically declaring a 2nd Table1, which can/does produce odd behavior as it technically creates Cartesian product. It's therefore very important to use the syntax UPDATE [Table Alias] when using the FROM clause in an UPDATE statement.

Upvotes: 2

Ven
Ven

Reputation: 2014

Ignore null value rows. This assumes you have one non null row per Id

UPDATE Table1 set Departure = Table2.departure
FROM TABLE1  INNER JOIN TABLE2 
ON TABLE1.Id = TABLE2.Id where Table2.canload =1 and Table2.deprture is not null

Upvotes: 1

Related Questions