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
    According to the SQL standard you need parentheses around the columns: where (COLUMN1, COLUMN2) IN (...) but I don't think SQL server supports this. – a_horse_with_no_name May 7 '15 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 May 7 '15 at 9:20
13

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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