SQL错误 - 语法错误
问题描述:
我有下面的T-SQL查询从一系列表中删除一条记录:SQL错误 - 语法错误
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'
但它不断抛出的错误:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INNER'.
看着它,我不确定我错过了什么。任何帮助表示赞赏。
答
需要两个栌我知道它的怪异
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'
如果你看一下online help这里的语法
[ 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
}
首先从是
FROM Is an optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited.
第二个从是
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).
正如托尼指出,你可以选择删除第一个FROM所以它有点更具可读性
DELETE
Config
FROM
config ....
答
我加了一些表的别名来清洁查询了一下,但关键是,你需要两个栌:一个用于DELETE,一个用于查询本身。
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'
答
或省略第一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'
您可以省略第一个FROM如果它使对眼睛:) DELETE FROM配置配置INNER JOIN ... – Tony 2010-12-13 16:43:50
@Tony感谢我更容易我更新了我的答案 – 2010-12-13 16:48:52