CODEWITHSUNDEEP
sqlsql-serverdatabase-migrationef-core-2.1
user989988
user989988

Reputation: 3746

SQL update from one table to another table based on an id

I have a database with 2 tables - Employees and Group.

The Employees has these columns:

Id (primary key), Name, GroupId

Group has these columns:

Id (primary key), EmployeeId (foreign key), GroupId

This is the Employees table:

+-----+------+---------+
| Id  | Name | GroupId |
+-----+------+---------+
| 123 | abc  | 000     |
| 456 | def  | 000     |
+-----+------+---------+

And this is the Group table:

+-----+------------+---------+
| Id  | EmployeeId | GroupId |
+-----+------------+---------+
| pqr | 456        | g01     |
| uvw | 123        | g02     |
| xyz | 123        | g03     |
+-----+------------+---------+

I need to get any one GroupId (for example top one) of an EmployeeId from Group table and update GroupId in Employees table.

The final result should like this:

+-----+------+---------+
| Id  | Name | GroupId |
+-----+------+---------+
| 123 | abc  | g01     |
| 456 | def  | g02     |
+-----+------+---------+

Please let me know if you have any solutions on the same. Thanks!

I'm using SQL Server.

Upvotes: 0

Views: 154

Answers (2)

Vishantha Peiris
Vishantha Peiris

Reputation: 101

This query will update Employee table from Group table and PK of group table is integer

UPDATE e
   SET GroupId = g.GroupId
  FROM Employees e INNER JOIN 
        (SELECT EmployeeId, GroupId FROM [Group] ORDER BY Id DESC) g 
        ON e.Id = g.EmployeeId

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Just use cross apply:

update e
    set groupid = g.groupid
    from employees e cross apply
         (select top (1) g.*
          from groups g
          where e.employeeid = g.employeeid
         ) g;

It seems strange to update employees with an arbitrary groupid. If you want one in particular -- such as the oldest -- you can add an order by clause to the subquery.

Upvotes: 1

Related Questions