delete语法问题

[dongshanyu]

Oracel中关联其他执行删除操作，目前我用的是SQL1的写法，是否有类似于SQL2的写法

SQL1:
-------------------------------------
DELETE
FROM table1 A
WHERE EXISTS
(
SELECT 1
FROM table2 B
WHERE 1 = 1
AND A.BID = B.BID
AND B.Type = 'abc'
);

SQL2:
-------------------------------------
DELETE table1
FROM table1 A
INNER JOIN table2 B ON (A.BID = B.BID)
WHERE 1 = 1
AND B.Type = 'abc';

[卖水果的net]

引用 4 楼 dongshanyu 的回复:

delete from (select t1.* from t1 inner join t2 on t1.c1 = t2.c1) 这个写法比较符合我的习惯，已测试，可行，谢谢

多多测试，找一下规律，看看哪个删的是哪一张表；

[sych888]

ORACLE通常用的是SQL1；SQL2好像用不了 SQL SERVER好像支持此语法

[卖水果的net]

SQL> 
SQL> col c1 format a10;
SQL> col c2 format a10;
SQL> col c3 format a10;
SQL> create table t1(c1 int, c2 int, c3 int);
Table created
SQL> create table t2(c1 int primary key, c2 int, c3 int);
Table created
SQL> insert into t1 values(1,2,3);
1 row inserted
SQL> insert into t1 values(2,2,3);
1 row inserted
SQL> insert into t2 values(1,2,3);
1 row inserted
SQL> insert into t2 values(2,2,3);
1 row inserted
SQL> delete from (select t1.* from t1 inner join t2 on t1.c1 = t2.c1);
2 rows deleted
SQL> select * from t1;
        C1         C2         C3
---------- ---------- ----------
SQL> select * from t2;
        C1         C2         C3
---------- ---------- ----------
         1          2          3
         2          2          3
SQL> drop table t1 purge;
Table dropped
SQL> drop table t2 purge;
Table dropped

SQL> 

[卖水果的net]

-- 官网有说明 http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8005.htm#i2112830

[dongshanyu]

To sych888 是的，SQL2的写法，SQL SERVER支持。 To wmxcn2000 感觉SQL1这种写法层次性较混乱， delete from (select t1.* from t1 inner join t2 on t1.c1 = t2.c1) 这个写法比较符合我的习惯，已测试，可行，谢谢