IF row exists THEN delete row in mysql

Question

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?

MySQL is (generally) not a procedural programming language; you can't use IF statements outside of a procedure. The documentation does state this. If you were to actually read it, you may also find the answer to your question. Is there some reason you were unable to read the documentation? — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Sep 28, 2015 at 13:22
Yeah. So you're pretty much stuck with doing the IF in your application logic. — Ifedi Okonkwo
– Ifedi Okonkwo, Commented Sep 28, 2015 at 13:24
@LightnessRacesinOrbit actually I saw this answer and thought maybe it is possible to using IF in mysql! anyway ok thanks. — Shafizadeh
– Shafizadeh, Commented Sep 28, 2015 at 13:27

Sergey Kalinichenko · Accepted Answer · 2015-09-28 13:24:17Z

14

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.

answered Sep 28, 2015 at 13:24

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

@Sajad As far as I know, only limit 1 works with MySql. TOP 1 is Sql Server syntax (related).

The LIMIT 1 is unnecessary; EXISTS sort of does that.