Multi-Part Identifier Error on Temp Table Where Exists

Question

I'm trying to delete records from #tableA that do not exist in #tableB. Seems pretty straightforward, however, I'm recieving the

multi-part identifier #TABLEA.MODELNO cannot be bound

even though "modelno" column exists in tableA. What am I missing?

DELETE FROM #TABLEA
WHERE EXISTS (SELECT 1 FROM #TABLEB WHERE #TABLEB.MODEL = #TABLEA.MODELNO)

I'd like to avoid using WHERE MODELNO IN() for performance reasons.

Answer 1

Your syntax is off, and the DELETE keyword should be followed by a target. Assuming you alias both tables, the following should work:

DELETE a
FROM #TABLEA a
WHERE NOT EXISTS (
    SELECT 1
    FROM #TABLEB b
    WHERE b.MODEL = a.MODELNO
);

If you wanted to use a join approach, the following anti-join with the help of a CTE should work:

WITH cte AS (
    SELECT a.*
    FROM #TABLEA a
    LEFT JOIN #TABLEB b
        ON b.MODEL = a.MODELNO
    WHERE b.MODEL IS NULL
)

DELETE FROM cte;