6

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.

2
  • 2
    According to the SQL standard you need parentheses around the columns: where (COLUMN1, COLUMN2) IN (...) but I don't think SQL server supports this.
    – user1822
    Commented May 7, 2015 at 9:18
  • 1
    @a_horse_with_no_name I had found this online as well, but SQL server indeed does not support this.
    – Reaces
    Commented May 7, 2015 at 9:20

1 Answer 1

14

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.