Deleting from Oracle SQL table using 'inner join'

Question

SO I've searched high and low, trying other tips used on this forum to no avail.

So trying to delete using inner join in Oracle SQL Developer (v3.2.20.09)

Table I wish to delete from (Table1, column name Column1), where the data matches the column 'Column2' in 'Table2.

I know there are some differences between Oracle/Microsoft SQL, tried multiple queries such as below, with slight variation (using open/close brackets, inner joins, WHERE EXISTS, WHERE (select's). TRY:

 delete from table2 where 
 exists (select column1 from table1);

 delete from table2, 
 inner join table1 on table2.column2 = table1.column1;

What are the problem(s) of the code that I wrote?

Consider using a MERGE. It contains a DELETE clause you can use. — Mr. Llama
– Mr. Llama, Commented Jun 20, 2016 at 14:41

user330315 · Accepted Answer · 2016-06-20 14:44:39Z

4

The EXISTS version would look like this:

delete from table2
where exists (select *
              from table1
              where table1.column1 = table2.column2);

Alternatively you can use an IN clause

delete from table2
where column2 in (select column1
                  from table1);

answered Jun 20, 2016 at 14:44

user330315

Sign up to request clarification or add additional context in comments.

Comments

sstan · Accepted Answer · 2016-06-20 14:45:44Z

3

If you're trying to delete from table1, then that's the table name that has to be used in the delete clause, not table2.

delete table1 t1
 where exists (select null
                 from table2 t2
                where t2.column2 = t1.column1)

answered Jun 20, 2016 at 14:45

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Collectives™ on Stack Overflow

Deleting from Oracle SQL table using 'inner join'

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related