SQL Syntax Error db_query

Question

I am trying to run he following query from Adminer 4.6.2 and it gives me the vague "syntax error" with no further details:

 db_query("DELETE FROM {field_collection_item} WHERE field_name NOT IN (SELECT field_name FROM {field_config} WHERE type = 'field_collection')");

I have also tried this:

DELETE FROM {field_collection_item} WHERE field_name NOT IN (SELECT field_name FROM {field_config} WHERE type = 'field_collection')

but it returns the same syntax error.

Do not use curly brackets, use square braces instead for table `DELETE FROM [field_collection_item] WHERE field_name NOT IN (SELECT field_name FROM [field_config] WHERE type = 'field_collection')` — Ven, Jul 12 '18 at 07:57

score 1 · Answer 1 · answered Jul 12 '18 at 08:07

Do not use double quotes, use 2 single quotes '', Filter condition requires value to be enclosed in single quotes. It is always a good practice to verify your code you can do that by using Print @sql

EX:

DECLARE @sql VARCHAR(max)

SET @sql = 'DELETE FROM [field_collection_item] WHERE field_name
               NOT IN (SELECT field_name FROM [field_config] WHERE type = ''field_collection'')'

PRINT @sql

Prints:

    DELETE FROM [field_collection_item] WHERE field_name NOT IN 
   (SELECT field_name FROM [field_config] WHERE type = 'field_collection')

SQL Syntax Error db_query

1 Answers1