'How can I add data from table to other in SQL?

I have 2 departments and created a new one, I want to update employee department but if that employees id equals one of id's listed in other table.

Employees

empid  empname  deptname
------------------------
01     john       dept1
02     bill       dept2
03     alex       dept1
.
.
.
80     tomas      dept1

New_depts_employees_id

empid
-----
02
05
45
18
20
34
78
80
55
32

If employee's id is inside the second table his depname will become 'dept3'

How can I write code make this process in SQL language (I using MS Access).

sqlms-access


Solution 1:[1]

Do you want sql? You can use update and exists as follows:

Update employees
   Set dept_name = 'dept3'
 Where exists (select 1 from New_depts_employees_id n where n.emp_id = employees.emp_id)

Solution 2:[2]

Open new query constructor.

Add both tables to it.

Drag Employees.empid and drop it onto New_depts_employees_id.empid - a link occures. Not needed if the link is created automatically.

Change query type to UPDATE.

Set "Column to update" to Employees.deptname.

Set "Value to set" to 'dept3'.

Click "Execute".

You may save this query and convert static 'dept3' value to query parameter for future use from external application. Or you may open query constructor in SQL Mode and copy query text from it for external use.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Popeye
Solution 2

Related Questions