0

I am wanting to delete records from a table if multiple columns in this table are in another table.

Currently, I usually just concatenate the columns and use the WHERE IN method:

DELETE FROM TableA
WHERE Col1+Col2 IN (SELECT Col1+Col2 FROM TableB)

Is there a better way to achieve the same result?

Thanks.

1
  • 1
    Hi - please take the time to register your account. That will give you notifications from the site when you get a new answer, etc. It also makes it easier to log in from another computer, or your phone, etc. Also, please take the tour. – Hannah Vernon Nov 6 '20 at 15:47
4

Typically, you'd want to join to the two tables together, and delete rows from TableA that match rows in TableB. Something like this:

DELETE a
FROM TableA a
    INNER JOIN TableB b ON a.Col1 = b.Col1
        AND a.Col2 = b.Col2;

Using the method you show in your question might result in the wrong rows being deleted. Here's an example to show what I mean:

IF OBJECT_ID(N'tempdb..#TableA', N'U') IS NOT NULL
BEGIN
    DROP TABLE #TableA;
END
IF OBJECT_ID(N'tempdb..#TableB', N'U') IS NOT NULL
BEGIN
    DROP TABLE #TableB;
END

CREATE TABLE #TableA
(
    i varchar(5) NOT NULL
    , j varchar(5) NOT NULL
);

CREATE TABLE #TableB
(
    i varchar(5) NOT NULL
    , j varchar(5) NOT NULL
);

INSERT INTO #TableA (i, j)
VALUES ('asdf', 'lk')
    , ('asd', 'flk');

INSERT INTO #TableB (i, j)
VALUES ('as', 'dflk')
    , ('asd', 'flk');

First, we'll see what values are in both tables:

SELECT *
FROM #TableA;

SELECT *
FROM #TableB;
╔══════╦═════╗
║  i   ║  j  ║
╠══════╬═════╣
║ asdf ║ lk  ║
║ asd  ║ flk ║
╚══════╩═════╝

╔═════╦══════╗
║  i  ║  j   ║
╠═════╬══════╣
║ as  ║ dflk ║
║ asd ║ flk  ║
╚═════╩══════╝

The intention would be to delete the bottom row, while retaining the top row, in TableA. However, if we run this:

DELETE FROM #TableA
WHERE #TableA.i + #TableA.j IN (SELECT #TableB.i + #TableB.j FROM #TableB);

SELECT *
FROM #TableA;

We'll see both rows are gone from TableA.

This statement, however, results in only the correct row being removed from TableA:

DELETE a
FROM #TableA a
    INNER JOIN #TableB b ON a.i = b.i
        AND a.j = b.j;
SELECT *
FROM #TableA;

Results:

╔══════╦════╗
║  i   ║ j  ║
╠══════╬════╣
║ asdf ║ lk ║
╚══════╩════╝
0

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.