CODEWITHSUNDEEP
mysqlsql
SBK
SBK

Reputation: 81

SQL - delete row if another table exists

I'm trying to delete a row from a table if another table doesn't exist. I've tried using the following statement

 IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user3project3'))
 BEGIN
     DELETE FROM user1table WHERE id=3
 END

However I get the following error:

 Unrecognized statement type. (near "IF" at position 0)

I'm using phpMyAdmin with XAMPP, if that matters. Thanks a lot in advance!

Upvotes: 1

Views: 167

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The IF statement is only allowed in programming blocks, which in practice means in stored procedures, functions, and triggers.

You could express this logic in a single query:

DELETE FROM user1table
    WHERE id = 3 AND
          NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user3project3');

That said, you have a very questionable data model if you are storing a separate table for each user.

Upvotes: 3

Related Questions