How to update based on other tables with MS SQL Server
Problem
How to update a table, but only for the rows that have correlating data? Such as:
- Only employees with sales will get a 10% increase in bonus.
- Need to make sure that employees without sale do not get any bonus increase.
MS SQL Server has 2 ways of doing this:
- WHERE IN subqueries
- WHERE EXISTS subqueries
Data used
Sales data:
select sales_date,sales_qty,sales_price, emp_short from sales where year(sales_date) = 2005; sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2005-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000110 2005-12-06 11:01:01.000 8.00 11.75 000010 2005-11-06 10:01:01.000 9.00 11.75 000010 2005-12-06 11:01:01.000 8.00 11.75 000010 2005-03-06 14:01:01.000 5.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000310 2005-12-06 11:01:01.000 8.00 11.75 000010 2005-04-06 15:01:01.000 4.00 11.75 000010 2005-05-06 16:01:01.000 3.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000030 2005-11-06 10:01:01.000 9.00 11.75 000310 2005-12-06 11:01:01.000 8.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000310 (15 row(s) affected)
Employee data:
select empno, firstnme, lastname, bonus from emps; empno firstnme lastname bonus ------ ------------ --------------- --------------------------------------- 000010 CHRISTINE HAAS 1000.00 000020 MICHAEL THOMPSON 800.00 000030 SALLY KWAN 800.00 000050 JOHN GEYER 800.00 000060 IRVING STERN 500.00 000070 EVA PULASKI 700.00 000090 EILEEN HENDERSON 600.00 000100 THEODORE SPENSER 500.00 000110 VINCENZO LUCCHESSI 900.00 000120 SEAN O CONNELL 600.00 000130 DOLORES QUINTANA 500.00 000140 HEATHER NICHOLLS 600.00 000150 BRUCE ADAMSON 500.00 000160 ELIZABETH PIANKA 400.00 000170 MASATOSHI YOSHIMURA 500.00 000180 MARILYN SCOUTTEN 500.00 000190 JAMES WALKER 400.00 000200 DAVID BROWN 600.00 000210 WILLIAM JONES 400.00 000220 JENNIFER LUTZ 600.00 000230 JAMES JEFFERSON 400.00 000240 SALVATORE MARINO 600.00 000250 DANIEL SMITH 400.00 000260 SYBIL JOHNSON 300.00 000270 MARIA PEREZ 500.00 000280 ETHEL SCHNEIDER 500.00 000290 JOHN PARKER 300.00 000300 PHILIP SMITH 400.00 000310 MAUDE SETRIGHT 300.00 000320 RAMLAL MEHTA 400.00 000330 WING LEE 500.00 000340 JASON GOUNOT 500.00 (32 row(s) affected)
Solution 1: WHERE IN subquery
- The problem is to make sure that employees without sales do NOT get any bonus.
- The answer is to have a subquery that verify that there are sales.
- Here is the list of the employees that will get the 11% bonus because they had sales.
- I am using the select because, the update will not show the updated records.
select empno, bonus as 'Old Bonus', (bonus * 1.11) as 'New Bonus'
from emps
where empno in
(select emp_short from sales where year(sales_date) = 2005);
empno Old Bonus New Bonus
------ --------------------------------------- ---------------------------------------
000010 1000.00 1110.0000
000030 800.00 888.0000
000110 900.00 999.0000
000190 400.00 444.0000
000310 300.00 333.0000
(5 row(s) affected)
Solution 2: WHERE EXISTS subquery
Instead of using:
where empno in
(select empno from sales where ...);
You can use:
where exists
(select ... from ... where ...);
select empno, bonus as 'Old Bonus', (bonus * 1.11) as 'New Bonus'
from emps
where exists
(select emp_short
from sales
where year(sales_date) = 2005 and
emps.empno = sales.emp_short);
empno Old Bonus New Bonus
------ --------------------------------------- ---------------------------------------
000010 1000.00 1110.0000
000030 800.00 888.0000
000110 900.00 999.0000
000190 400.00 444.0000
000310 300.00 333.0000
(5 row(s) affected)
Summary
- You MUST use a subquery to find out if there are corresponding entries in the child table.
- You CANNOT use a link table with a where clause that say:
where empno is not null;
This would update the employee bonus as many times as there there are corresponding entries in the sales file, and each time the bonus would be multiplied by 1.1.
- In this case, MS SQL Server has the same plan and the same cost for both methods. MS SQL Server does full table scans, since both table are small enough to fit in memory, and does NOT use any index because the table scans are faster [less I/O].
- My preference is to use the WHERE IN method [solution 1]