2

I have this query which returns the items that need to be deleted. What should the query be to delete these items.

SELECT p1.ID
FROM pidata_load p1
WHERE NOT EXISTS (SELECT p2.ID FROM pi_base p2 WHERE p1.pbid = p2.ID)

Is it safe to do this?

DELETE FROM pidata_load
WHERE NOT EXISTS (SELECT p2.ID FROM pi_base p2 WHERE pbid = p2.ID)
GO

Update 1 - Here is my query with an alias on the outer table

DELETE p1 FROM pidata_load AS p1 WHERE NOT EXISTS (SELECT p2.ID FROM pi_base p2 WHERE p1.pbid = p2.ID)
GO
the dave
  • 71
  • 1
  • 8
  • 1
    If your first query is returning the data you want, then yes, your second query will `delete` those rows. You should probably still alias the outer table though... – sgeddes Jul 20 '16 at 22:47
  • 1
    Yes and this is a very good practice to verify the where before you perform the delete – paparazzo Jul 20 '16 at 22:48
  • How would I alias the outer table? – the dave Jul 20 '16 at 22:58
  • 1
    alias the outer table thus: `DELETE pi FROM pidata_load AS pi WHERE NOT EXISTS ...` – Ross Presser Jul 20 '16 at 23:39
  • @sgeddes: I'm not convinced your suggested change is worth the bother but it does highlight the fact that `pi` is not a 'table alias'! Think about it literally: does "Delete table from table where search condition" make sense? No, because `pi` is a range variable and if it is an alias for anything then it would be a row i.e. `pi` 'ranges' over the table and when it satisfies the search condition then the row is deleted. – onedaywhen Jul 21 '16 at 09:27
  • @onedaywhen -- In general when using correlated subqueries I'd highly recommend aliasing the outer table. If the `pbid` field were in both tables, then this query would fail... Better to be safe in my opinion and explicitly define what you are correlating on. In regards to your other comment, `p1` is a table alias -- it is an alias to `pidata_load` in the first query. Just as `p2` is an alias to `pi_base` in the subquery. Not sure I'm following you there. – sgeddes Jul 21 '16 at 13:34
  • @RossPresser - I took that and changed my query using p1 instead of pi, I think that might have derailed some of the comments. – the dave Jul 21 '16 at 18:00
  • @sgeddes: re range variables: Are you a 'learn from a book' person? See citations in [this answer](http://stackoverflow.com/questions/996863/in-sql-what-is-the-letter-after-a-table-name-in-a-select-statement/1000362#1000362). Are you a 'try it myself' person? Try writing some [LINQ to Objects in C#](https://msdn.microsoft.com/en-us/library/mt693052.aspx), it's an interesting data language ('query syntax' rather than 'lambdas'). If you are a 'toe the industry line' person (nothing wrong with that) then ignore the mathematics, just call it a 'table alias' and don't learn anything new today :) – onedaywhen Jul 26 '16 at 09:50
  • @sgeddes: I'm not sure what you mean by 'to be safe' (either something works as intended or not) but we are in agreement on always including alias/range variables e.g. I can never remember which the 'outer' table is supposed to be! – onedaywhen Jul 26 '16 at 10:04

3 Answers3

2

Yes that will work. The DELETE statement works much like the SELECT statement, but it deletes the results.

Brendan Murphy
  • 93
  • 1
  • 2
  • 10
0

Yes, EXISTS is valid in the WHERE clause of a delete statement.

You can use IN to check against the Id as well. Pretty sure that this is less efficient than EXISTS. In my opinion, it is a little bit easier to read as well, but again know it is less efficient.

--With a query
SELECT p1.ID
FROM pidata_load p1
WHERE p1.ID NOT IN (SELECT p2.ID FROM pi_base)

--with regular values or parameters
SELECT p1.ID
FROM pidata_load p1
WHERE p1.ID NOT IN (1,2,3,4)
konkked
  • 3,161
  • 14
  • 19
0

Yes, definitely it safe to use delete statement instead of select.

It is good practice to test delete statement with select prior to execute.

Sandip - Frontend Developer
  • 14,397
  • 4
  • 35
  • 61