CODEWITHSUNDEEP
postgresqloracle-databasesql-delete
GiLA3
GiLA3

Reputation: 419

Delete .. from .. join, same syntax for postgresql and oracle

I need to execute the same exact query on oracle and postgresql, in which i need to delete rows from table 1, using a join on table 2. I already had a working query, but it stopped working for oracle with 1000+ results in the "IN" statement.

delete from t1 
where
t1.oid IN 
(SELECT oid from t2 WHERE [condition])

I've read about joins but postgresql uses the "using" keyword instead

DELETE [target table]  
FROM   [table1]  
       INNER JOIN [table2]  
ON     [table1.[joining column] = [table2].[joining column]  
WHERE  [condition]

Any help is appreciated, thanks

Upvotes: 2

Views: 484

Answers (2)

MT0
MT0

Reputation: 168232

You can use and multi-dimensional IN expression (which should work up to 100,000 items):

DELETE FROM t1 
WHERE ( t1.oid, 1 ) IN ( SELECT oid, 1 FROM t2 );

or EXISTS (which should work for any volume of rows):

DELETE FROM t1 
WHERE EXISTS ( SELECT 1 FROM t2 WHERE t1.oid = t2.oid );

And add the WHERE condition into the sub-query as required.

PostgreSQL db<>fiddle

Oracle db<>fiddle

Upvotes: 2

user330315
user330315

Reputation:

Oracle does not support a JOIN or USING or something similar for the DELETE statement. Your only choices are IN or EXISTS, if you need something that works on Postgres and Oracle.

If an IN condition is too slow then try an EXISTS condition:

delete from t1 
where exists (select * 
              from t2
              where t2.oid = t1.oid
                and [condition])

Upvotes: 4

Related Questions