SQL Syntax Error db_query

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.

1 answer

  • answered 2018-07-12 08:07 Ven

    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')