How to fill a table from another by condition?

Question

SQL Server

I have an Employee table:

pos_id is a foreign key to the Position table:


The Employee table has a position field in which the positions of employees are written, all those positions are in the Position table.

How can I query the pos_id so that the pos_id number in the Employee table matches the pos_id number in the Position table, while the position of the Employee table is equal to the pos_name of the Position table?

Answer 1

chain the tables by position (inner join) and then you can assign the id from the job directory to the id of the employee directory

update Employee 
set pos_id=Position.pos_id -- очевидно что слева обновляемая таблица
from Emploee
inner join Position on Position.pos_name=Employee.position

And most importantly, after that, link the tables, drop Employee.position so that not a single dog can score a non-existent pos_id for an
employee. it might be useful to pre-evaluate select with a similar join to make sure most or all of the entries "match" and possibly wrap both sides of the bundle in ltrim(rtrim(...))