patternsqlModerate
Delete using a composite key
Viewed 0 times
keyusingdeletecomposite
Problem
I am trying to wrap my head around creating a delete statement for a table with a composite key.
I would like to create something like:
Keeping in mind only the combination of
I can't seem to get my head around how I would actually do this.
I would like to create something like:
DELETE
FROM
table_example1
WHERE
COLUMN1, COLUMN2
IN
(SELECT COLUMN1, COLUMN2 FROM table_example2
WHERE
COLUMN_DATETIME > @Period);Keeping in mind only the combination of
COLUMN1 and COLUMN2 is unique, each column on its own is not unique.I can't seem to get my head around how I would actually do this.
Solution
Should be something like this:
Alternatively:
DELETE A
FROM
table_example1 AS A
INNER JOIN table_example2 AS B
ON A.COLUMN1 =B.COLUMN1
AND A.COLUMN2 = B.COLUMN2
WHERE
COLUMN_DATETIME > @Period;Alternatively:
DELETE FROM A
FROM dbo.table_example1 AS A
WHERE EXISTS
(
SELECT *
FROM dbo.table_example2 AS B
WHERE
B.COLUMN1 = A.COLUMN1
AND B.COLUMN2 = A.COLUMN2
AND B.COLUMN_DATETIME > @Period
);Code Snippets
DELETE A
FROM
table_example1 AS A
INNER JOIN table_example2 AS B
ON A.COLUMN1 =B.COLUMN1
AND A.COLUMN2 = B.COLUMN2
WHERE
COLUMN_DATETIME > @Period;DELETE FROM A
FROM dbo.table_example1 AS A
WHERE EXISTS
(
SELECT *
FROM dbo.table_example2 AS B
WHERE
B.COLUMN1 = A.COLUMN1
AND B.COLUMN2 = A.COLUMN2
AND B.COLUMN_DATETIME > @Period
);Context
StackExchange Database Administrators Q#100845, answer score: 14
Revisions (0)
No revisions yet.