Oracle Delete Rows Matching on Multiple Values

Oracle Delete Rows Matching On Multiple Values

No, you just need parentheses:

DELETE FROM student WHERE
(student.course, student.major) IN
(SELECT schedule.course, schedule.major FROM schedule)

Delete on matching multiple values supplied as static list

If you're deleting matching pairs then the syntax would be:

DELETE FROM os_abp_classification
WHERE (class_key, uprn) IN (
  select '2810C000000635', 28006900 from dual
  union all select '2810C000000636', 28006901 from dual
  union all select '2810C000000637', 28006902 from dual
  union all ...
)

Depending on where your strings are coming from, you could make this more general by splitting those up into separate elements

delete from os_abp_classification
where (class_key, uprn) in (
  with class_keys as (
    select level as rn,
      regexp_substr('2810C000000635,2810C000000636,2810C000000637',
        '[^,]+', 1, level) as class_key
    from dual
    connect by regexp_substr('2810C000000635,2810C000000636,2810C000000637',
      '[^,]+', 1, level) is not null
  ),
  uprns as (
    select level as rn,
      cast(regexp_substr('28006900,28006901,28006902',
        '[^,]+', 1, level) as number) as uprn
    from dual
    connect by regexp_substr('28006900,28006901,28006902',
      '[^,]+', 1, level) is not null
  )
  select ck.class_key, u.uprn
  from class_keys ck
  join uprns u on u.rn = ck.rn
);

Longer with the sample data but if you have a lot of value pairs it might end up shorter, and you might be able to bind the string values depending on where/how you're running this.

The subquery uses a CTE to convert each original string of values into a list of values - left as strings in the first one, cast to numbers in the second. It also assigns a pseudo-row-number to each result in both sets which will be the same for the matching pairs, so then joining the CTEs together gives you the pairs in a form ready for the in clause:

SQL Fiddle showing the results of the CTE join as pairs, the delete based on that, and the rows remaining afterwards.

---

If you already have lists of values you could also do this:

delete from os_abp_classification
where (class_key, uprn) in (
  with class_keys as (
    select rownum as rn, column_value as class_key
    from table(sys.odcivarchar2list('2810C000000635','2810C000000636','2810C000000637'))
  ),
  uprns as (
    select rownum as rn, column_value as uprn
    from table(sys.odcinumberlist(28006900,28006901,28006902))
  )
  select ck.class_key, u.uprn
  from class_keys ck
  join uprns u on u.rn = ck.rn
);

... but relying on rownum on a query without an explicit order by is risky; it looks OK here but may not be guaranteed to work, and could break in the future even if it does work now. odcivarchar2list is defined as a vararry, which is an ordered set. I think Tom Kyte sort of implies it's OK too. But even so I'd be wary of using this. (Is that enough caveats?)

Oracle Delete Rows Matching On Multiple Values

No, you just need parentheses:

DELETE FROM student WHERE
(student.course, student.major) IN
(SELECT schedule.course, schedule.major FROM schedule)

Deleting multiple rows in oracle

You can do this:

delete from mytable
where (id, name) in ((1, 'xyz'),
                     (2, 'abc'),
                     (3, 'abc'));

Delete from table where multiple fields match select subquery result from other table

You can try with merge:

merge into table1 t1
  using (select t2.circuit_id from table2 t2 inner join table3 t3 on t2.circuit_name = t3.sc_prod_service_id) d
  on (d.circuit_id = t1.carriedby_circuit or d.circuit_id = t1.carried_circuit)
when matched then delete;

Deleting rows based on multiple values in another table

Your brackets are dodgy...

DELETE FROM TABLE15 p
  WHERE (p.item_id, p.product_id) IN
           (SELECT S.item_id, S.product_id
              FROM TABLE14 S
                   );

You need to remove the additional one. The error is caused by in not being parsed correctly due to the extra bracket.

Optimal way to DELETE specified rows from Oracle

In advance of my questions being answered, this is how I'd go about it:

Minimize the number of statements and the work they do issued in relative terms.

All scenarios assume you have a table of IDs (PURGE_IDS) to delete from TABLE_1, TABLE_2, etc.

Consider Using CREATE TABLE AS SELECT for really large deletes

If there's no concurrent activity, and you're deleting 30+ % of the rows in one or more of the tables, don't delete; perform a create table as select with the rows you wish to keep, and swap the new table out for the old table. INSERT /*+ APPEND */ ... NOLOGGING is surprisingly cheap if you can afford it. Even if you do have some concurrent activity, you may be able to use Online Table Redefinition to rebuild the table in-place.

Don't run DELETE statements you know won't delete any rows

If an ID value exists in at most one of the six tables, then keep track of which IDs you've deleted - and don't try to delete those IDs from any of the other tables.

CREATE TABLE TABLE1_PURGE NOLOGGING
AS 
SELECT ID FROM PURGE_IDS INNER JOIN TABLE_1 ON PURGE_IDS.ID = TABLE_1.ID;

DELETE FROM TABLE1 WHERE ID IN (SELECT ID FROM TABLE1_PURGE);

DELETE FROM PURGE_IDS WHERE ID IN (SELECT ID FROM TABLE1_PURGE);

DROP TABLE TABLE1_PURGE;

and repeat.

Manage Concurrency if you have to

Another way is to use PL/SQL looping over the tables, issuing a rowcount-limited delete statement. This is most likely appropriate if there's significant insert/update/delete concurrent load against the tables you're running the deletes against.

declare
  l_sql varchar2(4000);
begin
  for i in (select table_name from all_tables 
             where table_name in ('TABLE_1', 'TABLE_2', ...)
             order by table_name);
  loop
    l_sql := 'delete from ' || i.table_name || 
             ' where id in (select id from purge_ids) ' || 
             '   and rownum <= 1000000';
    loop
      commit;
      execute immediate l_sql;
      exit when sql%rowcount <> 1000000;  -- if we delete less than 1,000,000
    end loop;                             -- no more rows need to be deleted!
  end loop;
  commit;
end;

Delete rows from table which does not exists in other table by picking eligible records only

Edit: I misread your query originally. I have changed my answer to remove the join to Sample_Table in the first condition.

Delete from FINAL_TABLE FT
where not exists 
(select 1 from 
Latest_table LT
where LT.ID = FT.ID
and LT.NAME = FT.NAME
and LT.SUB_ID = FT.SUB_ID)
AND FT.id IN
(
 SELECT id FROM Sample_Table
)

This will only delete from Final_Table if the id appears in Sample_Table and the record (all 3 columns) does not appear in the Latest_table.

An alternative way of writing this is

Delete from FINAL_TABLE FT
where 
  (FT.ID, FT.SUB_ID, FT.NAME) NOT IN
   (SELECT LT.ID, LT.SUB_ID, LT.NAME FROM Latest_table LT)
 AND FT.ID IN
  (SELECT ST.id FROM Sample_Table ST)

---

Related Topics