Update multiple columns from a specific row in another table

Question

I have seen examples of updating multiple rows from a join, but in this case there are more than one matching rows resulting from a join, I need a specific row (the earliest UpdatedDate row).

Example of the tables:

[Robot]

RobotNumber         RobotName    RobotColor      UpdatedDate   
1                   XA01         Red             01/01/2020
2                   B1205        Purple          02/02/2020
3                   ZP344        Orange          03/03/2020

[RobotAuditTable]

RobotNumber         RobotName    RobotColor      UpdatedDate   
1                   XA01         Yellow          1/1/2019
1                   XA01         Red             2/2/2020

I want to update the RobotName XA01 with the values(RobotName, RobotColor) from the earliest Updated Date row in the RobotAuditTable.

I have tried approaches with joins and subqueries but I cannot get the syntax and conditions correct.

Gordon Linoff · Accepted Answer · 2020-06-30 00:14:31Z

1

You can use apply:

update r
    set r.robotname = rat.robotname,
        r.robotcolor = rat.robotcolor        
    from robot r cross apply
         (select top (1) rat.*
          from robotaudittable rat
          where rat.robotnumber = r.robotnumber
          order by rat.updateddate asc
         ) rat;

answered Jun 30, 2020 at 0:14

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Update multiple columns from a specific row in another table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related