IF object_id('emp1', 'U') is not null DROP TABLE emp1;
SELECT * INTO emp1 FROM emp;

-- CORRELATED SUBQUERY

-- A = slow
-- SELECT * FROM emp1 
UPDATE emp1 SET sal=sal+1000
WHERE isnull(comm,0) = 0 AND
	((SELECT loc FROM dept WHERE deptno=emp1.deptno) IN ('CHICAGO', 'DALLAS')) AND
	((SELECT count(1) FROM emp1 subEmp1 WHERE subEmp1.job=emp1.job)>1);

-- B = views = list
-- key-preserved view
-- goodLocationPeople, goodJobPeople, jobCounts

IF object_id('emp1', 'U') is not null DROP TABLE emp1;
IF object_id('goodLocationPeople', 'V') is not null DROP VIEW goodLocationPeople;
IF object_id('jobCounts', 'V') is not null DROP VIEW jobCounts;
IF object_id('goodJobPeople', 'V') is not null DROP VIEW goodJobPeople;
GO
SELECT * INTO emp1 FROM emp;
GO
CREATE VIEW goodLocationPeople AS
	SELECT empno
	FROM emp1 INNER JOIN dept ON (emp1.deptno = dept.deptno)
	WHERE loc IN ('DALLAS', 'CHICAGO');
GO
CREATE VIEW jobCounts AS
	SELECT count(1) as jobCount, job
	FROM emp1
	GROUP BY job;
GO
CREATE VIEW goodJobPeople AS
	SELECT empno
	FROM emp1 INNER JOIN jobCounts ON (emp1.job=jobCounts.job)
	WHERE jobCount>1;
GO
-- SELECT * FROM goodLocationPeople;
-- SELECT * FROM goodJobPeople;
UPDATE emp1 SET sal=sal+1000
WHERE isnull(comm, 0) = 0 AND
	empno IN (SELECT * FROM goodLocationPeople) AND
	empno IN (SELECT * FROM goodJobPeople);

-- C: UPDATE + JOIN
-- Oracle: Updateable inline view UPDATE (SELECT xxxx) 
-- Mysql: UPDATE table INNER JOIN t2 INNER JOIN t3
-- Mssql: UPDATE table SET xxx=yyy FROM table INNER JOIN t2 INNER JOIN t3
-- Postgre: UPDATE table SET xxx=yyy FROM t2, t3 WHERE xxxx

UPDATE emp1 SET sal=sal+1000
-- SELECT * 
FROM emp1 
	INNER JOIN goodLocationPeople ON (emp1.empno=goodLocationPeople.empno)
	INNER JOIN goodJobPeople ON (emp1.empno = goodJobPeople.empno)
WHERE isnull(comm, 0) = 0;