MS SQL Server 6 - UPDATE from a SELECT in SQL Server

Hello All,

Welcome back to SQL Server article.

In this article we will learn that "How to update the table from select statement"

Is it Possible?

Yes you heard right, In SQL Server, it is possible to insert the rows into a table with the help of INSERT.. SELECT statement

and also it is possible to update a table with SELECT

It sounds good right, let begin with example

1. Lets Create the  new database EmpManagerDB

create database EmpManagerDB

2. use/open created database EmpManagerDB

use EmpManagerDB

3. create table called employees

create table employees
(
	empid				int primary key,
	fullname			nvarchar(200),
	emailId				nvarchar(200),
	phone				varchar(100),
	training_required	        varchar(3),
	status				varchar(10)
)

4. Insert records to the table employees

insert into employees values(1,'chakri','chakri@test.com',

'9089786756',null,'active')
insert into employees values(2,'shraddha','shraddha@test.com',

'8899007766',null,'active')
insert into employees values(3,'shreshta','shreshta@test.com',

'7788990066',null,'active')

5. Lets view the records inserted to table

In the above result we can see the training_required column has null values

6. Create another table called employee_project

create table employee_project
(
	emp_prj_id	 int primary key,
	empid		 int,
	project_name varchar(200)
)

7. Insert some records to the table employee_project

insert into employee_project values (1,1,'Banking Project')
insert into employee_project values (2,2,'Health Care Project')

8.  Lets view the records inserted to table

9. Lets update the employees table column training_required, if employee assigned to project then its Null else Yes.

We need to assign the training, when employees does not have projects

update emp
set emp.training_required = 'Yes'
from employees emp
where emp.empid not in (select empid from employee_project)

10. Lets, view the records inserted to table

As we discussed above, only one employee does not have project, so training assigned to him/her.

That's it for this article, see you in next article, until then take care bye...