DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to synchronize tables with correlated subquery?

Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate the delete of records in a table which are not in another table.


-- SQL select into table create for test data
USE tempdb;
SELECT SalesOrderID = CONVERT(INT,SalesOrderID),
       OrderDate,
       TotalDue
INTO   SOH
FROM   AdventureWorks.Sales.SalesOrderHeader
GO
-- (31465 row(s) affected)
 
-- SQL insert - add extra records with non-matching SalesOrderID-s
-- MSSQL insert select - T-SQL top function
INSERT SOH
SELECT TOP 1000 SalesOrderID + 100000,
                OrderDate + 2,
                TotalDue + 1000
FROM   AdventureWorks.Sales.SalesOrderHeader
GO
-- (1000 row(s) affected)
 
-- SQL delete table - delete non-matching records
DELETE x
FROM   SOH AS x
WHERE  NOT EXISTS (SELECT 1
                   FROM   AdventureWorks.Sales.SalesOrderHeader y
                   WHERE  x.SalesOrderID = y.SalesOrderID)
GO
-- (1000 row(s) affected)

 
-- SQL alternate delete syntax
-- MSSQL delete table - delete non-matching records
DELETE SOH
WHERE  NOT EXISTS (SELECT *
                   FROM   AdventureWorks.Sales.SalesOrderHeader y
                   WHERE  SOH.SalesOrderID = y.SalesOrderID)
GO

 
 
-- Cleanup
DROP TABLE SOH

GO
 

------------

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE