CODEWITHSUNDEEP
sqloracle-databaseinner-joinsql-delete
thalassophile
thalassophile

Reputation: 275

Delete two tables based on results of one join

I am trying to delete data from two tables at the same time using inner join. However when I tried to run my query, an error

SQL command not properly ended

error came out.

A brief background of what I am trying to do and some info on the tables, table1 and table2. So both tables has a same field, for instance "ABC". I would like to delete data from both tables using inner join but under the where condition of a field (XYZ) under table where it equals to a value.

This is my sql statment:

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

Upvotes: 0

Views: 422

Answers (4)

William Robertson
William Robertson

Reputation: 15991

A PL/SQL solution might be something like this:

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;

Output:

Stored 1000 values for processing
Deleted 1000 rows from table1
Deleted 1000 rows from table1

Test setup:

create table table1 (abc, xyz) as
    select rownum, 'TESTIT' from dual connect by rownum <= 1000
    union all
    select rownum, 'OTHER' from dual connect by rownum <= 100;

create table table2 as select * from table1;

After deletion there are 100 rows in each table. I have assumed we only want to delete the ones where xyz = 'TESTIT' even when abc values are common to both tables.

Upvotes: 0

Shane
Shane

Reputation: 9

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

Upvotes: -1

Joe Taras
Joe Taras

Reputation: 15379

You can't delete more than one table.

You must use two different DELETE statements.

For this you can create a temporary table to store IDs to delete, for example:

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;

Upvotes: 2

XING
XING

Reputation: 9886

In Oracle you cannot delete from 2 tables in a single statement like you are doing. The syntax is wrong. You can use as below:

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

Upvotes: 1

Related Questions