CODEWITHSUNDEEP
sql
R0bert
R0bert

Reputation: 557

Deleting a row from a table based on the existence

This might be a trivial solution. I have searched similar posts regarding this but I couldn't find a proper solution.

I'm trying to delete a row if it exists in a table

I have a table say

         Table1
----------------------------
|Database| Schema | Number |
----------------------------
|  DB1   |   S1   |    1   | 
|  DB2   |   S2   |    2   |
|  DB3   |   S3   |    3   | <--- Want to delete this row
|  DB4   |   S4   |    4   |
----------------------------

Here is my query

DELETE FROM Table1
WHERE EXISTS
(SELECT * FROM Table1 WHERE Database = 'DB3' and Schema = 'S3');

When I tried the above SQL, it returned me an empty table, don't understand why it's returning an empty table.

There are similar posts on stack overflow but I couldn't find why I'm getting empty table.

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Why are you using a subquery? Just use a where clause:

DELETE FROM Table1
WHERE Database = 'DB3' and Schema = 'S3';

Your code will delete either all rows or no rows. The where condition is saying "delete all rows from this table where this subquery returns at least one row". So, if the subquery returns one row, everything is deleted. Otherwise, nothing is deleted.

Upvotes: 2

Related Questions