I want to delete records from both tables in the JOIN from the this CTE in SQL Server 2012:
WITH RECORDS_TO_DELETE ([Counter], [ID])
AS
(
SELECT D.[Counter], D.[ID], C.[ID]
FROM [Table1] AS D
LEFT JOIN
(
SELECT [ID] FROM [Table2]
WHERE NOT LOWER (COL1) = '*my criteria*'
AND (Date1 <= '31-Mar-2010' OR Date2 <= '31-Mar-2010')) AS C
ON D.ID = C.ID
)
DELETE FROM RECORDS_TO_DELETE
I have seen other examples that say its important to do this in the correct order but those examples are not in CTEs. I used a CTE because I have a convoluted WHERE clause (perhaps complicated by the fact my JOIN is to a result set) that I haven't felt necessary to include here. A CTE was the only thing that selected the data I wanted deleted.
Is there a way to delete records from both tables where there is a match between both tables?
In order to delete the records from both tables, use your CTE to identify the primary key values for all of the records in both tables that you want to delete and write those values somewhere (table variable, temp table, actual table, probably based on the volume of your data).
Then execute two DELETE statements, one against each table, joining on the key values for the table in question.
You'll need that table to hold your values since, after you delete from the first table, your CTE won't work to identify the needed records anymore.
WITH cte AS
(
INSERT YOUR LOGIC HERE
)
INSERT INTO @KeyValuesHoldingTable
SELECT
*
FROM
cte;
DELETE Table1
FROM
Table1 as t
JOIN
@KeyValuesHoldingTable as h
ON t.KeyValues = h.KeyValues;
DELETE Table2
FROM
Table2 as t
JOIN
@KeyValuesHoldingTable as h
ON t.KeyValues = h.KeyValues;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments