SQL Server Update statement with JOIN

In this article, we are specifically going to see a practical usage of the UPDATE statement in SQL Server when the condition is based on JOIN of another table.

Example:

CREATE TABLE EMP
       (EMPNO Integer Primary Key,
        ENAME Varchar(15),
        JOB Varchar(9),
        MGR Integer,
        HIREDATE datetime,
        SAL Integer,
        COMM Integer,
        DEPTNO Integer);
go
INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902, '12/17/80',
        800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        '5/20/81',1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        '5/22/81', 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        '4/2/81',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        '9/28/81', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        '5/1/81',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        '6/9/81',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        '12/9/82', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        '11/17/81', 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        '9/8/1981',  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        '1/12/83', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        '12/3/81',   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        '12/3/81',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        '1/23/82', 1300, NULL, 10);
go

CREATE TABLE DEPT
       (DEPTNO INTEGER Primary Key,
        DNAME VARCHAR(14),
        LOC VARCHAR(13) );
go
INSERT INTO DEPT VALUES
        (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
        (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
        (40,'OPERATIONS','BOSTON');

Let us see the two tables.

select * from emp
go
select * from dept
go

Here we can see that this is a typical parent-child table relationship. Many a times, the end user wants to see the Employee Names (Enames) and Department Names (Dnames). But for that, they need to surrender to the inner join technique. Right now, only 14 rows are in emp table and 4 rows in dept.

But in a real time production environment, the number of records will be much higher. So this may result into a performance overhead. Therefore the DBA in such scenarios may decide to add the DName column in Emp table itself to eliminate the a JOIN overhead.

Note: Here we are moving towards an intentional De-Normalization technique.

The following ALTER Table command is used for that.

Alter Table Emp
Add Dname Varchar(40)

But now the problem is that for existing records if we see the DName colum, then it will be Null. Let us check it.

select * from emp

So now we want to update each Dname as per the Dname of Dept table. For this we will issue the Update statement with join.

Update Emp 
set Dname  = Dept.DName
From Emp, Dept
where Emp.Deptno = Dept.Deptno

Technique: Here the update technique with join is used in the following sequential steps.

1. Every record of Emp is taken.
2. Its deptno is matched with deptno of Dept table.
3. The corresponding DName of Dept table is assigned to that row.
4. Next row of Emp is taken.
5. Step numbers 2 & 3 are repeated.
6. This process continues till the last row is reached by Emp table.

So now let us see the Emp table again.

select * from emp

and that’s how we can use the Update statement with JOIN in SQL Server.