SQL Error - bad syntax
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.
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 ....
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'
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'
精彩评论