ItemAccounts
ItemCode    AccountCode  ItemCodeAccount
123           ABBC         A123
123           ZZZY         A123

poitmvnd
item_no    Vend_no       vend_item_no
123         ZZ            A123

cicmpy
AccountCode    Vend_no
ABBC             AB
ZZZY             ZZ

quote:

---

Originally posted by Vack

What If I have the following:

ItemAccounts
ItemCode    AccountCode  ItemCodeAccount
123           ABBC         A123
123           ZZZY         A123

poitmvnd
item_no    Vend_no       vend_item_no
123         ZZ            A123

cicmpy
AccountCode    Vend_no
ABBC             AB
ZZZY             ZZ

Won't the script not delete anything since its going to find a match? I need to add the accountcode in there somehow don't I?

---

-- 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

-- 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;

quote:

---

Originally posted by Vack

I'm looking for the 1st row in the itemaccounts table to be the only record deleted because it does not exist in the poitmvnd table.

---

quote:

---

Originally posted by Vack

I'm looking for the 1st row in the itemaccounts table to be the only record deleted because it does not exist in the poitmvnd table.

---

quote:

---

Originally posted by Vack

I was able to work around this by creating SQL view for the POITMVND table and adding the accountcode to it. Then Visakh16's first suggestion worked by adding account code to the where clause.

Thanks for everyone's help.

---