0
DELETE 
  FROM 014_terms 
 WHERE term_id IN (SELECT term_id 
                     FROM 014_term_taxonomy 
                    WHERE taxonomy LIKE 'pa_%');

I get an error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE term_id IN (SELECT term_id FROM 014_term_taxonomy WHERE taxonomy LIKE '...' at line 1

NOTE: I am using a plain text editor and the spaces are just plain spaces.

I am using phpMyAdmin and I have tried different variations of the query wrapping the table name in single quotes, like this :

SQL query:

DELETE FROM '014_terms' WHERE term_id IN (SELECT term_id FROM '014_term_taxonomy' WHERE taxonomy LIKE 'pa_%')

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''014_terms' WHERE term_id IN (SELECT term_id FROM '014_term_taxonomy' WHERE t...' at line 1

and this

SQL query:

DELETE FROM '014_terms' WHERE term_id IN (SELECT term_id FROM 014_term_taxonomy WHERE taxonomy LIKE 'pa_%')

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''014_terms' WHERE term_id IN (SELECT term_id FROM 014_term_taxonomy WHERE tax...' at line 1
user11777
  • 1
  • 1
  • 2
    Table name can start with a digit? – Eric Sep 23 '20 at 17:33
  • 2
    @Eric [Identifier names may begin with a numeral, but can't only contain numerals unless quoted](https://mariadb.com/kb/en/identifier-names/). – kmoser Sep 23 '20 at 17:35
  • Does it work with an alias ? DELETE t FROM 014_terms t WHERE ... – Sélim Achour Sep 23 '20 at 17:35
  • 1
    You may have a bad character before the `where`. – Gordon Linoff Sep 23 '20 at 17:36
  • 1
    Wouldn't hurt to try quoting the table names anyway, e.g. \`014_terms\`. – kmoser Sep 23 '20 at 17:36
  • There's nothing wrong with the syntax as @GordonLinoff mentioned, there must be a bad character. As @kmoser mentioned try adding `014_terms` and `014_term_taxonomy` and you can even add to columns too – Shreyas B Sep 23 '20 at 17:38
  • @kmoser I tried quotes (see updated question above) but didn't work. Also, it isn't a problem with a bad character. – user11777 Sep 24 '20 at 00:19
  • 1
    Not single quotes, use the back-tick `\`` characters. For me, that's on the key to left of 1 on my keyboard, usually on the same key with `~`. Do the same with the table name in the subquery too. – Paul T. Sep 24 '20 at 00:20
  • Thanks @PaulT. - The backticks worked. – user11777 Sep 24 '20 at 13:23

2 Answers2

0

The final solution was to use backticks to wrap the table names that begin with a numerical prefix like this. Thanks to @PaulT.

DELETE FROM `014_terms` WHERE term_id IN (SELECT term_id FROM `014_term_taxonomy` WHERE taxonomy LIKE 'pa_%');

Thanks to everyone who provided feedback!!

:-)

user11777
  • 1
  • 1
-1

The problem in your case seems like the table name starting from numbers/digits.

Try updating your sql_mode:

SET @@session.sql_mode=ANSI_QUOTES;

After the above change, try executing your query.

DELETE
FROM 014_terms
WHERE term_id IN
    (SELECT term_id
     FROM 014_term_taxonomy
     WHERE taxonomy LIKE 'pa_%');
Ankit Jindal
  • 3,672
  • 3
  • 25
  • 37