Updating column in a table from another table using join

Question

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.

empty RotationOrder indicates null or empty string.?

Sahi
– Sahi

2018-05-24 09:43:46 +00:00
Commented May 24, 2018 at 9:43 — Sahi
– Sahi, Commented May 24, 2018 at 9:43
RoationOrder have data

Eldho
– Eldho

2018-05-24 09:44:56 +00:00
Commented May 24, 2018 at 9:44 — Eldho
– Eldho, Commented May 24, 2018 at 9:44

George Menoutis · Accepted Answer · 2018-05-24 09:47:37Z

2

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

edited May 24, 2018 at 9:47

answered May 24, 2018 at 9:34

George Menoutis

7,3004 gold badges23 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thom A · Accepted Answer · 2018-05-24 09:44:41Z

2

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.

edited May 24, 2018 at 9:44

answered May 24, 2018 at 9:34

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

6 Comments

Eldho Over a year ago

I dont see a order by clause in the query. Wont it takes arbitrary value from the second table

Thom A Over a year ago

@Eldho why would a UPDATE statement need an ORDER BY clause? The order you update the rows is meaningless.

Eldho Over a year ago

If i have more than 1 match in the joining table i.e, table 2 it should take first element sorted by the rotation order. How will achieve that

Thom A Over a year ago

@Eldho This is why adding expected results is so important in your questions. :)

George Menoutis Over a year ago

Thank for the "Notice" comment, I edited my answer according to it.

|

Ven · Accepted Answer · 2018-05-24 09:34:44Z

1

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

answered May 24, 2018 at 9:34

Ven

2,0241 gold badge15 silver badges28 bronze badges

1 Comment

Eldho Over a year ago

I cannot Table2.deprture is not null if its null it should be updated too. How this will get the first element from table 2 is there are many matches.

Collectives™ on Stack Overflow

Updating column in a table from another table using join

3 Answers 3

Comments

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related