CODEWITHSUNDEEP
mysqlif-statementexists
Shafizadeh
Shafizadeh

Reputation: 10340

IF row exists THEN delete row in mysql

I have a query like this:

IF EXISTS(SELECT 1 FROM table1 WHERE col1 = ? LIMIT 1) THEN
DELETE FROM table2 WHERE col2 = ?
END IF

But I don't know why above query does not work. Also this does not work too:

IF  EXISTS(SELECT 1 FROM table1 WHERE col1 = ? LIMIT 1) BEGIN
DELETE FROM table2 WHERE col2 = ?
END

MySQL tell my there is a syntax error, How can I fix it?

Upvotes: 7

Views: 30669

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726559

You can move the condition into the WHERE clause of DELETE to achieve the same effect, like this:

DELETE FROM table2
WHERE col2 = ?
  AND EXISTS(SELECT 1 FROM table1 WHERE col1 = ? LIMIT 1)

Note that the two ?s have switched places with relation to the original query.

Upvotes: 14

Related Questions