CODEWITHSUNDEEP
mysql
user3830572
user3830572

Reputation: 23

Check if record exists delete it using mysql

i'm using MySQL and i want to check if a record exists and if it exists delete this record.

i try this but it 's not working for me:

SELECT 'Barcelone' AS City, EXISTS(SELECT 1 FROM mytable WHERE City = 'Barcelone') AS 'exists';

THEN

DELETE FROM mytable
WHERE City = 'Barcelone';

Thank you for your help.

Upvotes: 1

Views: 5095

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The if statement is only allowed in stored procedures, stored functions, and triggers (in MySQL).

If I understand what you want, just do:

DELETE FROM mytable
WHERE City = 'Barcelone';

There is no reason to check for the existence beforehand. Just delete the row. If none exist, no problem. No errors.

I would recommend an index on mytable(city) for performance reasons. If you want to check if the row exists first, that is fine, but it is unnecessary for the delete.

Upvotes: 3

spencer7593
spencer7593

Reputation: 108400

If you mean MySQL is returning an error message (if that's what you mean by "not working for me"), then that's exactly the behavior we would expect.

That SQL syntax is not valid for MySQL.

If you want to delete rows from a table, issue a DELETE statement, e.g.

DELETE FROM mytable WHERE City = 'Barcelone'

If you want to know how many rows were deleted (if the statement doesn't throw an error), immediately follow the DELETE statement (in the same session) with a query:

SELECT ROW_COUNT()

Or the appropriate function in whatever client library you are using.

If the ROW_COUNT() function returns 0, then there were no rows deleted.

There's really no point (in terms of MySQL) in issuing a SELECT to find out if there are rows to be deleted; the DELETE statement itself will figure it out.

If for some reason your use case requires you to check whether there are rows be be deleted, then just run a separate SELECT:

SELECT COUNT(1) FROM mytable WHERE City = 'Barcelone'

Upvotes: 1

Related Questions