SQL Error - bad syntax

Question

I have the following T-SQL query to delete a record from a series of tables:

DELETE FROM config INNER JOIN config_profile ON config.config_id = config_profile.config_id
INNER JOIN config_page ON config_profile.config_profile_id = config_page.config_profile_id
INNER JOIN config_field ON config_page.config_page_id = config_field.config_page_id
INNER JOIN config_constraint ON config_field.config_field_id = config_constraint.config_field_id
INNER JOIN config_constraint_type ON config_constraint.config_constraint_type_id = config_constraint_type.config_constraint_type_id
WHERE config.config_name = 'ConfigName' AND config_profile.profile_name = 'ProfileName'

But it keeps throwing the error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INNER'.

Looking at it, I'm not sure what I'm missing. Any help is appreciated.

Conrad Frix · Accepted Answer · 2010-12-13 16:48:39Z

You need two FROMs I know its wierd

DELETE 
FROM CONfig 
FROM 
config 
INNER JOIN config_profile ON config.config_id = config_profile.config_id
INNER JOIN config_page ON config_profile.config_profile_id = config_page.config_profile_id
INNER JOIN config_field ON config_page.config_page_id = config_field.config_page_id
INNER JOIN config_constraint ON config_field.config_field_id = config_constraint.config_field_id
INNER JOIN config_constraint_type ON config_constraint.config_constraint_type_id = config_constraint_type.config_constraint_type_id
WHERE config.config_name = 'ConfigName' AND config_profile.profile_name = 'ProfileName'

If you look at the online help here's the syntax

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP (expression ) [ PERCENT ] ] 
    [ FROM ] 
    { <object> | rowset_function_limited 
      [ WITH ( <table_hint_limited> [ ...n ] ) ]
    }
    [ <OUTPUT Clause> ]
    [ FROM <table_source> [ ,...n ] ] 
    [ WHERE { <search_condition> 
            | { [ CURRENT OF 
                   { { [ GLOBAL ] cursor_name } 
                       | cursor_variable_name 
                   } 
                ]
              }
            } 
    ] 
    [ OPTION ( <Query Hint> [ ,...n ] ) ] 
[; ]

<object> ::=
{ 

    [ server_name.database_name.schema_name. 
      | database_name. [ schema_name ] . 
      | schema_name.
    ]
    table_or_view_name 
}

The first from is

FROM Is an optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited.

The second From is

FROM Specifies an additional FROM clause. This Transact-SQL extension to DELETE allows specifying data from and deleting the corresponding rows from the table in the first FROM clause.

This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed.

For more information, see FROM (Transact-SQL).

As Tony points out you can optionally Drop the first FROM so its a bit more readable

DELETE 
   Config 
FROM 
   config ....

You can omit the first FROM if it makes it easier on the eye :) DELETE config FROM config INNER JOIN ...

Joe Stefanelli · Accepted Answer · 2010-12-13 16:42:25Z

I added some table aliases to clean the query up a bit, but the key is that you need two FROMs: one for the DELETE and one for the query itself.

DELETE FROM c
    FROM config c
        INNER JOIN config_profile cp
            ON c.config_id = cp.config_id
        INNER JOIN config_page cpg
            ON cp.config_profile_id = cpg.config_profile_id
        INNER JOIN config_field cf
            ON cpg.config_page_id = cf.config_page_id
        INNER JOIN config_constraint cc
            ON cf.config_field_id = cc.config_field_id
        INNER JOIN config_constraint_type cct
            ON cc.config_constraint_type_id = cct.config_constraint_type_id
    WHERE c.config_name = 'ConfigName' 
        AND cp.profile_name = 'ProfileName'

HLGEM · Accepted Answer · 2010-12-13 16:54:21Z

0

Or omitting the first FROM

DELETE c 
    FROM config c 
        INNER JOIN config_profile cp 
            ON c.config_id = cp.config_id 
        INNER JOIN config_page cpg 
            ON cp.config_profile_id = cpg.config_profile_id 
        INNER JOIN config_field cf 
            ON cpg.config_page_id = cf.config_page_id 
        INNER JOIN config_constraint cc 
            ON cf.config_field_id = cc.config_field_id 
        INNER JOIN config_constraint_type cct 
            ON cc.config_constraint_type_id = cct.config_constraint_type_id 
    WHERE c.config_name = 'ConfigName'  
        AND cp.profile_name = 'ProfileName'

answered Dec 13, 2010 at 16:54

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Collectives™ on Stack Overflow

SQL Error - bad syntax

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related