HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Delete using a composite key

Submitted by: @import:stackexchange-dba··
0
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:

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:

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.