CODEWITHSUNDEEP
sql
GoGetSOme
GoGetSOme

Reputation: 121

Delete from memory table if in another in memory table

I have 2 in memory tables @Holding and @P1

if the row is in @P1 I want to remove it from @Holding

With my code below the duplicates are not being removed. I do not have a primary Id key like many examples, For a unique row I have Drawing, Method and Location.

DELETE h
FROM @Holding h
INNER JOIN @P1 p1 ON p1.DRAWING = h.DRAWING and p1.Method = h.Method and 
p1.LOCATION = h.LOCATION

Upvotes: 0

Views: 997

Answers (2)

James
James

Reputation: 3015

if your duplicates are not being removed, is because the requirement to join with @P1 to find them is incorrect.

Just to show an example that the delete/join works as expected:

declare @t1 table (c1 int)
declare @t2 table (c1 int)
insert into @t1 values (1),(2),(3)
insert into @t2 values (1)

delete @t1
from @t1 t1
  join @t2 t2
    on t1.c1=t2.c1

select * from @t1

enter image description here

Upvotes: 0

Lawrence
Lawrence

Reputation: 347

You could try using a where exists

DELETE h
FROM @Holding h
WHERE EXISTS (
    SELECT *
    FROM @P1 p1
    WHERE p1.DRAWING = h.DRAWING and p1.Method = h.Method and p1.LOCATION = h.LOCATION
)

Upvotes: 1

Related Questions