这个DELETE可以吗? [英] Is this DELETE possible?
本文介绍了这个DELETE可以吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当我尝试使用
从表中删除表中的值时,我收到错误消息。目的是删除
TableA中的所有行,其中col_2匹配任何col_1值。
DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON( x.col_1 =
y.col_2)
错误消息:表''TableA''含糊不清。
这可以用SQL完成,还是我在这里使用带游标的T-SQL?
I am getting error messages when I try to delete from a table using
the values in the table itself. The intent is to delete all rows from
TableA where col_2 matches any of the col_1 values.
DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
y.col_2)
Error msg: The table ''TableA'' is ambiguous.
Can this be done with SQL or should I use T-SQL with cursors here?
推荐答案
尝试EXISTS或IN
DELETE TableA
WHERE EXISTS(SELECT * FROM TableA y
WHERE TableA.col_2 = y.col_1)
或者你想要:
DELETE TableA
WHERE EXISTS(SELECT * FROM TableA y
WHERE TableA。 col_1 = y.col_2)
一如既往,我建议您先使用SELECT查询进行测试。 (没有保证
隐含等等......)
" php newbie" <是ne ********** @ yahoo.com>在消息中写道
新闻:12 ************************** @ posting.google.c om ...
当我尝试使用
从表中删除表中的值时,我收到错误消息。目的是删除
TableA中的所有行,其中col_2匹配任何col_1值。
DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON( x.col_1 =
y.col_2)
错误消息:表''TableA''含糊不清。
这可以用SQL完成,还是我在这里使用带游标的T-SQL?
Try EXISTS or IN
DELETE TableA
WHERE EXISTS (SELECT * FROM TableA y
WHERE TableA.col_2 = y.col_1)
Or did you want:
DELETE TableA
WHERE EXISTS (SELECT * FROM TableA y
WHERE TableA.col_1 = y.col_2)
As always, I recommend you test with SELECT queries first. (No warrantees
implied, etc...)
"php newbie" <ne**********@yahoo.com> wrote in message
news:12**************************@posting.google.c om...
I am getting error messages when I try to delete from a table using
the values in the table itself. The intent is to delete all rows from
TableA where col_2 matches any of the col_1 values.
DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
y.col_2)
Error msg: The table ''TableA'' is ambiguous.
Can this be done with SQL or should I use T-SQL with cursors here?
试试:
DELETE来自x
来自TableA x
INNER JOIN TableA y ON(x.col_1 = y.col_2)
-
希望这会有所帮助。
Dan Guzman
SQL Server MVP
" php newbie" <是ne ********** @ yahoo.com>在消息中写道
新闻:12 ************************** @ posting.google.c om ...
Try:
DELETE FROM x
FROM TableA x
INNER JOIN TableA y ON (x.col_1 = y.col_2)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"php newbie" <ne**********@yahoo.com> wrote in message
news:12**************************@posting.google.c om...
当我尝试使用表格中的值从表中删除时,我收到错误消息。目的是删除表格中的所有行,其中col_2与col_1的任何值匹配。
从表格中删除表格A来自内部联合表格和开启(x.col_1 =
y.col_2)
错误消息:表''TableA'是不明确的。
这可以用SQL完成,还是我应该在这里使用T-SQL和游标?
I am getting error messages when I try to delete from a table using
the values in the table itself. The intent is to delete all rows from
TableA where col_2 matches any of the col_1 values.
DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
y.col_2)
Error msg: The table ''TableA'' is ambiguous.
Can this be done with SQL or should I use T-SQL with cursors here?
Aaron,
感谢您提示。它有效!
在相关的说明中,当我尝试使用来自同一TableA的数据更新
TableA时,我遇到了同样的错误。
我使用的命令是:
UPDATE TableA SET col_2 = y.col_2
FROM TableA x INNER JOIN TableA y
ON(x.col_1 = y.col_1)
错误信息是:表''TableA''不明确。
你有类似的解决方案吗?
" Aaron W. West" < TA ****** @ hotmail.NO.SPAM>在消息新闻中写道:< P4 ******************** @ speakeasy.net> ...
Aaron,
Thanks for the tip. It worked!
On a related note, I am facing the same error when I try to update
TableA with data from the same TableA.
The command I used is this:
UPDATE TableA SET col_2 = y.col_2
FROM TableA x INNER JOIN TableA y
ON (x.col_1 = y.col_1)
Error message is: The table ''TableA'' is ambiguous.
Do you have a similar solution?
"Aaron W. West" <ta******@hotmail.NO.SPAM> wrote in message news:<P4********************@speakeasy.net>...
尝试EXISTS或IN
DELETE TableA
WHERE EXISTS(SELECT * FROM TableA y
WHERE TableA.col_2 = y.col_1)
一如既往,我建议您使用SELECT进行测试首先查询。 (没有保证等暗示,等等......)
Try EXISTS or IN
DELETE TableA
WHERE EXISTS (SELECT * FROM TableA y
WHERE TableA.col_2 = y.col_1)
As always, I recommend you test with SELECT queries first. (No warrantees
implied, etc...)
这篇关于这个DELETE可以吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文