CODEWITHSUNDEEP
sqloracle-databasejoin
LJM4rru
LJM4rru

Reputation: 25

Deleting from Oracle SQL table using 'inner join'

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?

Upvotes: 2

Views: 7352

Answers (2)

sstan
sstan

Reputation: 36483

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)

Upvotes: 3

user330315
user330315

Reputation:

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

Upvotes: 4

Related Questions