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, Sep 28 '15 at 13:22
Yeah. So you're pretty much stuck with doing the `IF` in your application logic. — Ifedi Okonkwo, Sep 28 '15 at 13:24
@LightnessRacesinOrbit actually I saw [this answer](http://stackoverflow.com/questions/639854/check-if-a-row-exists-otherwise-insert#639874) and thought maybe it is possible to using `IF` in mysql! anyway ok thanks. — Shafizadeh, Sep 28 '15 at 13:27

score 14 · Accepted Answer · answered Sep 28 '15 at 13:24

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 '15 at 13:24

@Sajad As far as I know, only `limit 1` works with MySql. `TOP 1` is Sql Server syntax ([related](http://stackoverflow.com/q/5668540/335858)). – Sergey Kalinichenko Sep 28 '15 at 14:19
1

The `LIMIT 1` is unnecessary; `EXISTS` sort of does that. – Rick James Jan 05 '16 at 02:10

1 Answers1