delete tableName from ...from ... 这两个form是怎么回事?

[DD88]

今天看见一个SQL语句:
delete from ... from ...

感觉很奇怪,于是试写了一下.
设两个表结构如下
test1
t1ID | t1Name
---------------
2 | 张三
3 | 李四

test2
t2ID | t2Name
---------------
7 | 王五

执行下列语句
delete from Test1
from ((select * from Test1) t1
join
(select * from Test2) t2
on t1.t1ID = t2.t2ID)
那么, Test1表数据没有被删除任何一条

但是, 如果Test2表数据如下:
t2ID | t2Name
---------------
2 | 王五

那么Test1表中的两条数据都被删除了~~

---------------------------------

不知道这个写法可不可以这么理解: 如果两表某字段存在相同数据, 那么就清空其中一个表的所有数据.

真奇怪~~

在mysql中试了, mysql 不支持这种from ... from的古怪写法...

[hyljyrw]

delete 后面加 from ,我也没见过这种写法呀？
讨论吧大家。

[gimy007]

所要删除某表里的记录时，需要根据关联另外一个表，就这样写

[MorningTea]

楼主语法不大对

感觉呀，见过的是

Delete from Test1
Inner join Test2
On Test1.col1 = Test2.col1
.....

[vivianfdlpw]

语法
DELETE
[ FROM ]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}

[ FROM { < table_source > } [ ,...n ] ]

[ WHERE
{ < search_condition >
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
] }
}
]
[ OPTION ( < query_hint > [ ,...n ] ) ]

< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ]
| rowset_function [ [ AS ] table_alias ]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table >

< joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition >
| < table_source > CROSS JOIN < table_source >
| < joined_table >

< join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
[ < join_hint > ]
JOIN

< table_hint_limited > ::=
{ FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}

< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}

< query_hint > ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
}

[vivianfdlpw]

delete from Test1
from ((select * from Test1) t1
join
(select * from Test2) t2
on t1.t1ID = t2.t2ID)

========>
等价于
delete Test1
from ((select * from Test1) t1
join (select * from Test2) t2
on t1.t1ID = t2.t2ID)

[vivianfdlpw]

前边的from是可以省略的

[panjinfu80]

没有见过这样的写法的。

[superunusa]

我认为也是第一个FROM是余的，可以省略。就是要删除T1表中在T2表中也有的数据

[DD88]

to superunusa(赵天云)
我试了一下,好像不是那么回事.只要T2表中有和T1重复的数据(某字段),T1表就会被清空.
不知道怎么回事.

[prcgolf]

up

[xueguang]

delete from 应该是标准sql里的写法,就像 insert into 一样

[DD88]

那么, 大家说说,这种方法是不是表示,如果T2表中某字段也存在和T1表中某字段一样的数据,那么就清空T1表???
对吗? 从结果上来看,似乎是如此的.

[zxbyhcsdn]

Select From 能出来的,我想Delete From 就可以吧!!

[凌雯]

没有错，这是利用类似子查询的方式写的啊
你可以从代码是看出来是要删除表1中的数据，但这种不太好很少有人这样写除非是一个一系列的操作中才有可以出现这种情况，
前现的一个 from 可以省略不写也不会有影响，