Here is a guess using a LEFT JOIN and a MERGE statement:-- Set up sample data
DECLARE @ItemAccounts TABLE (ItemCode INT, AccountCode VARCHAR(10), ItemCodeAccount VARCHAR(10))
INSERT @ItemAccounts
VALUES
(123, 'ABBC', 'A123'),
(123, 'ZZZY', 'A123')
DECLARE @poitmvnd TABLE (item_no INT, Vend_no CHAR(2), vend_item_no VARCHAR(10))
INSERT @poitmvnd
VALUES
(123, 'ZZ', 'A123')
DECLARE @cicmpy TABLE (AccountCode VARCHAR(10), Vend_no CHAR(2))
INSERT @cicmpy
VALUES
('ABBC', 'AB'),
('ZZZY', 'ZZ')
-- Pre Delete
SELECT *
FROM @ItemAccounts
-- Do actual delete
DELETE IA
FROM @ItemAccounts AS IA
LEFT OUTER JOIN
(
SELECT c.*
FROM
@poitmvnd AS p
INNER JOIN
@cicmpy AS c
ON p.Vend_no = c.Vend_no
) AS t
ON IA.AccountCode = T.AccountCode
WHERE T.AccountCode IS NULL
-- Post delete
SELECT *
FROM @ItemAccounts
Using a MERGE statement:-- Do actual delete
MERGE
@ItemAccounts AS Target
USING
(
SELECT c.*
FROM
@poitmvnd AS p
INNER JOIN
@cicmpy AS c
ON p.Vend_no = c.Vend_no
) AS Source
ON Source.AccountCode = Target.AccountCode
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
Delete if records do not exist in another table
Posted - 07/25/2012 : 12:22:32
