求DB2 delete sql 语句

[whoami1618]

CREATE
TABLE X
(
A VARCHAR(10) NOT NULL,
B INTEGER NOT NULL,
CONSTRAINT CC1315882009531 PRIMARY KEY (A, B)
);

CREATE
TABLE Y
(
A VARCHAR(10) NOT NULL,
B INTEGER NOT NULL,
CONSTRAINT CC1315882056921 PRIMARY KEY (A, B)
);

TABLE X
A B
--------------
10 1
10 2
20 1
20 2
30 1
30 2
30 3
30 4
40 3
40 4

TABLE Y
A B
--------------
10 1
10 2
10 3
10 4
20 1
20 2
20 3
20 4

求一条SQL，将Y表中与X表重复的数据删除，即从Y表删除下列查询结果的数据：
SELECT x.a,x.b FROM x,y where x.a=y.a and x.b=y.b
10 1
10 2
20 1
20 2

[wwwwb]

试试
delete from
(select y.* from y left join x on X.A = Y.A and X.B = Y.B where x.a is not null) t

[whoami1618]

[Quote=引用 1 楼 zhaojianmi1 的回复:]
SQL code
delete from Y where exists (
select * from X where X.A = Y.A and X.B = Y.B
)
[/Quote]
不知效率怎样

[whoami1618]

[Quote=引用 2 楼 wwwwb 的回复:]
试试
delete from
(select y.* from y left join x on X.A = Y.A and X.B = Y.B where x.a is not null) t
[/Quote]
运行不通过呀，谢谢

[zhaojianmi1]

delete from Y where exists (
  select * from X where X.A = Y.A and X.B = Y.B
)