CODEWITHSUNDEEP
sqlsubquery
John Kiernan
John Kiernan

Reputation: 79

DELETE FROM subquery with multiple columns

I need to find out how to delete records based on subquery.

I've tried a lot of things and scoured here so as to not duplicate the question, but couldn't find an answer.

Here is the subquery:

SELECT TOP (100) PERCENT t.id_col, t.dept_no, t.unit, t.lease_star
 FROM trans AS t FULL OUTER JOIN
 temp_lease AS l ON t.dept_no = l.dept_no AND t.unit = l.unit AND t.lease_star = l.lease_start_date 
WHERE (l.dept_no IS NULL)
ORDER BY t.dept_no, t.unit, t.lease_star

The subquery finds records in the trans file that do not have a matching combination of dept_no, unit, and lease_start_date (the two files have different names for the last field, as VFP truncates field names down to 10 -- and they upsized a VFP file).

Having found those records (and it does), I then want to be able to DELETE the records that don't have that triple match in the temp_lease table.

To begin with, I would want to SELECT instead of DELETE, as I always learned to only use a DELETE once you're assured of the correct code.

Can anyone help me solve this one out? I've tried every variation nothing seems to work.

Upvotes: 1

Views: 4259

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Based on your description, I would use not exists. To get the rows to delete:

SELECT t.*
FROM trans t 
WHERE NOT EXISTS (SELECT 1
                  FROM temp_lease l 
                  WHERE t.dept_no = l.dept_no AND
                        t.unit = l.unit AND 
                        t.lease_start = l.lease_start_date 
                 );

Deleting the records requires just a tweak to the query:

DELETE t
FROM trans t 
WHERE NOT EXISTS (SELECT 1
                  FROM temp_lease l 
                  WHERE t.dept_no = l.dept_no AND
                        t.unit = l.unit AND 
                        t.lease_start = l.lease_start_date 
                 );

A full join is basically never useful in a delete. You need a record in the first table in order to delete it. Hence, a left join is sufficient.

Also, the above does not consider two columns to match when both are NULL. If that is an issue, you can tweak to the correlation clause to handle NULL values.

Upvotes: 1

Jay Rindani
Jay Rindani

Reputation: 40

DELETE FROM trans WHERE id_col in( SELECT TOP (100) PERCENT t.id_col FROM trans AS t FULL OUTER JOIN temp_lease AS l ON t.dept_no = l.dept_no AND t.unit = l.unit AND t.lease_star = l.lease_start_date WHERE (l.dept_no IS NULL) ORDER BY t.dept_no, t.unit, t.lease_star) innerquery

--Inner query - it run faster

 DELETE FROM trans 
 WHERE id_col in([ID_LIST_FROM_1st_QUERY])

Hope it helps.

Upvotes: 0

tony _008
tony _008

Reputation: 727

You could do something like this:

 DELETE FROM trans
 WHERE id_col in(
 SELECT TOP (100) PERCENT t.id_col
 FROM trans AS t FULL OUTER JOIN
 temp_lease AS l ON t.dept_no = l.dept_no AND t.unit = l.unit AND t.lease_star = l.lease_start_date 
WHERE (l.dept_no IS NULL)
ORDER BY t.dept_no, t.unit, t.lease_star) innerquery

If you insist on doing a SELECT FIRST, you could run the inner query first, and later do this:

 DELETE FROM trans 
 WHERE id_col in([ID_LIST_FROM_1st_QUERY])

I'd go with the first though, because after you have tested enough is fine if you delete what you don't need (and having them separated will delete anyway).

Hope it helps.

Upvotes: 1

Related Questions