How to Delete Records from Table A Based on Table B in SQL Server 2008

sql-server-2008

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.

Best Answer

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 ║
╚══════╩════╝

Related Solutions

Sql-server – In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns

Martin Smith's answer will serve very well to get you all the columns you need for an entire database in SQL 2008. Very nice!

Here is how I did it in the days before SQL had CTEs and PIVOT. This will be compatible with older versions of SQL where Martin's solution won't work, and still works in 2008 as well, but with poorer performance than his solution.

USE MyDB

SET NOCOUNT ON

CREATE TABLE ##nullable  (
    ID INT IDENTITY(1,1),
    SchName VARCHAR(128),
    TblName VARCHAR(128),
    ColName VARCHAR(128),
    hasNulls BIT,
    PRIMARY KEY(ID)
    )

DECLARE @currTbl VARCHAR(128)
DECLARE @currCol VARCHAR(128)
DECLARE @currSch VARCHAR(128)
DECLARE @limit INT
DECLARE @i INT
DECLARE @sql NVARCHAR(4000)

INSERT INTO ##nullable (
    SchName,
    TblName,
    ColName,
    hasNulls
    )
SELECT
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    0 AS hasNulls
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.IS_NULLABLE = 'YES'
AND t.TABLE_TYPE = 'BASE TABLE'

SET @limit = (SELECT MAX(ID) FROM ##nullable)
SET @i = 1

WHILE @i <= @limit
BEGIN   
    SELECT @currSch = SchName,
        @currTbl = TblName,
        @currCol = ColName
    FROM ##nullable 
    WHERE ID = @i

    SET @sql = 'UPDATE ##nullable 
        SET hasNulls = 1
        WHERE ID = ' + CAST(@i AS VARCHAR(20)) + ' 
        AND EXISTS (SELECT 1 FROM ' + QUOTENAME(@currSch) + '.'+ QUOTENAME(@currTbl) + ' 
        WHERE ' + QUOTENAME(@currCol) + ' IS NULL)'

    EXEC(@sql)

    SET @i = @i + 1     
END

SELECT DISTINCT * FROM ##nullable 
WHERE hasNulls = 0  

DROP TABLE ##nullable

Sql-server – Merge multiple table output but indicate which tables the records came from

You can use a full outer join between your tables and check against null values to figure out where the data comes from.

select isnull(A.ID, B.ID) as ID,
       isnull(A.Name, B.Name) as Name,
       case
         when A.ID is null then 'B'
         when B.ID is null then 'A'
         else 'A+B'
       end as Tables   
from TableA as A
  full outer join TableB as B
    on A.ID = B.ID

Best Answer

Related Solutions

Sql-server – In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns

Sql-server – Merge multiple table output but indicate which tables the records came from

Related Question