根据一个联接的结果删除两个表

相关文章推荐

星星上的墨镜 · 触发器精讲· 2 周前 ·

求醉的斑马 · 【C++】单例模式中的析构函数_c++单键需 ...· 4 天前 ·

腹黑的消炎药 · 培养中国式“南丁格尔”，南京南丁格尔护理学院 ...· 8 月前 ·

长情的弓箭 · 樱花动漫带你领略无限动漫世界的精彩_作品_工 ...· 1 年前 ·

豪爽的吐司 · 东吴证券：坚定拥抱汽车AI智能化和出海两条主线· 1 年前 ·

玩命的炒粉 · 秋红染醉通辽城 _中国网草原频道· 1 年前 ·

卖萌的葡萄酒 · 萌站-ACGN网站 - Typecho Wiki· 1 年前 ·

腾讯云

备案控制台

开发者社区

TVP

文章/答案/技术大牛

写文章

根据一个联接的结果删除两个表

DELETE table1, table2
FROM table1 
INNER JOIN table1 ON table1.ABC = table2.ABC
WHERE table1.XYZ = 'TESTIT';

DELETE table1   
where table1.ABC = (select table2.ABC 
                   from table2 
                   WHERE table2.ABC = table1.ABC
                   and table1.XYZ = 'TESTIT');

CREATE TABLE app (ABC varchar(100))
INSERT INTO app (ABC)
SELECT abc
FROM table1
INNER JOIN table1 ON table1.ABC = table2.ABC
WHERE table1.XYZ = 'TESTIT';
DELETE 
FROM table1 
WHERE table1.ABC IN (SELECT ABC FROM app);
DELETE 
FROM table2 
WHERE table2.ABC IN (SELECT ABC FROM app);
DROP TABLE app;

select distinct table1.ABC into Temptable
FROM table1 
INNER JOIN table1 ON table1.ABC = table2.ABC
WHERE table1.XYZ = 'TESTIT'
delete table1 where ABC in (select ABC from Temptable)
delete table2 where ABC in (select ABC from Temptable)
drop table Temptable

declare
    type abc_tt is table of table1.abc%type index by pls_integer;
    l_abc_collection abc_tt;
begin
    select distinct t1.abc bulk collect into l_abc_collection
    from   table1  t1
           join table2 t2 on t2.abc = t1.abc
    where t1.xyz = 'TESTIT';
    dbms_output.put_line('Stored ' || l_abc_collection.count || ' values for processing');
    forall i in 1..l_abc_collection.count
        delete table1 t
        where  t.xyz = 'TESTIT'
        and    t.abc = l_abc_collection(i);
    dbms_output.put_line('Deleted ' || sql%rowcount || ' rows from table1');
    forall i in 1..l_abc_collection.count
        delete table2 t
        where  t.xyz = 'TESTIT'
        and    t.abc = l_abc_collection(i);
    dbms_output.put_line('Deleted ' || sql%rowcount || ' rows from table2');
end;

Stored 1000 values for processing
Deleted 1000 rows from table1

根据一个联接的结果删除两个表

4 个回答