Delete two tables based on results of one join

Question

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';

Does your table names really have spaces in them? If so, put the table names in quotes (DELETE "table 1" ...) — MNos, Jan 05 '18 at 08:05
Hi @MNos, nope, my table doesnt have space in between them, I will edit the question to reduce confusion, thank you! — thalassophile, Jan 05 '18 at 08:10
Possible duplicate of [Delete a child and a parent row with one SQL script](https://stackoverflow.com/questions/9640340/delete-a-child-and-a-parent-row-with-one-sql-script) — trincot, Jan 05 '18 at 08:13
`FROM table1 INNER JOIN table1` is meant to be `FROM table1 INNER JOIN table2`? And is the column `ABC` one of the table's primary (or unique) keys? — Thorsten Kettner, Jan 05 '18 at 08:25
Documentation for `delete` is [here](https://docs.oracle.com/database/121/SQLRF/statements_8005.htm). You will notice there is no `join` clause. — William Robertson, Jan 05 '18 at 11:08

score 2 · Answer 1 · answered Jan 05 '18 at 08:14

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;

score 1 · Answer 2 · answered Jan 05 '18 at 08:10

1

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');

answered Jan 05 '18 at 08:10

XING

9,608
4
22
38

No `FROM` needed? – jarlh Jan 05 '18 at 08:17
`FROM` is always optional in Oracle. – XING Jan 05 '18 at 08:18
It is? Thanks for the info. – jarlh Jan 05 '18 at 08:19
1

@jarlh But mind it ,,its only optinal in DELETE statement but not in select :-) – XING Jan 05 '18 at 08:20
Hi, thanks for the reply, but how can I delete the second table? do I use changed the position of the tables in the query? – thalassophile Jan 05 '18 at 08:29
This is a correlated query. So change the table names and coditions are matched, then rows will be deleted, You need to try it. – XING Jan 05 '18 at 08:59
@XING: No. When deleting from table1 first, you cannot look up these records in order to delete from table2 - they will be gone :-) – Thorsten Kettner Jan 05 '18 at 09:06
@thalassophile: You haven't answered the question, whether `ABC` is one of the tables' primary or unique key. Is `table2.ABC` unique? Is `table1.ABC` unique or maybe `table1.ABC` + `table1.XYZ`? The answer to these questions can make a big difference. – Thorsten Kettner Jan 05 '18 at 09:09
@ThorstenKettner Didn't i say if the coditions are matched then records will be deleted and its common sense that if i deleted the records from the table then condition will never be matched. Isn't it? – XING Jan 05 '18 at 09:29
@XING: Well, maybe I am misunderstanding. But you are showing how to delete from one of the tables. OP is asking how to delete from the other table also. You say: change the statement accordingly. But how? OP wants to delete from t1 where there is a match in t2 and from t2 where there is a match in t1. So if you delete from one table, you cannot delete from the other afterwards because the lookup data will be gone. – Thorsten Kettner Jan 05 '18 at 09:35
@ThorstenKettner Frankly speaking question is not clear as in what OP wanted to do actually. I just showed an example since his actual query was not correct. Rest clarification you yourself asked. – XING Jan 05 '18 at 09:36

William Robertson · Answer 3 · 2018-01-05T11:43:50.107

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.

Shane · Answer 4 · 2018-01-05T08:21:44.537

-1

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

edited Jan 05 '18 at 08:21

answered Jan 05 '18 at 08:18

Shane

9
2

The idea is OK but `select into` does something different in Oracle - you would need `insert ... select`. – William Robertson Jan 05 '18 at 11:30
While this code may answer the question, providing additional context regarding *why* and/or *how* this code answers the question improves its long-term value. – Alexander Jan 05 '18 at 18:34

Delete two tables based on results of one join

4 Answers4