DELETE query with Common Table Expression ("WITH AS " clause)

Question

I am trying to delete 41,6% of my old rows from my oracle data table ( senstrig is a date format):

DELETE FROM
    (WITH RS AS (SELECT * FROM OLD_WIFISIGN WHERE SENSID= 1 ORDER BY SENSTRIG ASC))
WHERE ROWNUM <= (SELECT COUNT (*)/ 2.4 FROM RS);

But it returns an error:

ORA-00928 missing SELECT

I have already tried several versions but with no luck.

Could you please help me how can I make this "delete from" runnable?

I'd put WITH before DELETE. I.e. something like WITH RS ... DELETE FROM RS WHERE ... — jarlh
– jarlh, Commented Nov 28, 2019 at 12:11
To help you find information, that WITH AS syntax is called a "Common Table Expression" or "CTE"; I've edited the question to include that keyword. — IMSoP
– IMSoP, Commented Nov 28, 2019 at 12:15

Ponder Stibbons · Accepted Answer · 2019-11-28 12:29:05Z

1

Test table, 1000 rows, all with sensid = 1:

create table old_wifisign (sensid, senstrig) as (
  select 1, trunc(sysdate) - level 
    from dual connect by level <= 1000);

Delete:

delete 
  from old_wifisign  
  where rowid in (
      select rowid 
        from (
          select rowid, row_number() over (order by senstrig) / count(1) over () rto 
            from old_wifisign 
            where sensid = 1 )
        where rto <= .416)

Result: 416 rows with oldest senstrig deleted. Note that only sensid 1 is taken into calculations here, as in your query.

answered Nov 28, 2019 at 12:29

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

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

Comments

Marmite Bomber · Accepted Answer · 2019-11-28 13:13:51Z

1

To delete 41,6% of old rows check fist the boundary date to delete and than use it.

The analytic function PERCENT_RANK gives you the youngest senstrig you do not want to delete.

with perc_rank as (
select SENSID, SENSTRIG,
PERCENT_RANK()  OVER (ORDER BY senstrig) AS pr
from old_wifisign)
select max(SENSTRIG) from perc_rank 
where pr < .416

Than you simple takes this date and performs a DELETE

delete from old_wifisign
where SENSTRIG < 
(with perc_rank as (
  select SENSID, SENSTRIG,
  PERCENT_RANK()  OVER (ORDER BY senstrig) AS pr
  from old_wifisign) 
select max(SENSTRIG) from perc_rank 
where pr < .416
);

Anyway you should take soem care considering ties - thing about a case when half of the rows in the table have identical senstrig

answered Nov 28, 2019 at 13:13

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

1 Comment

Marmite Bomber Over a year ago

You are welcome @AkosVincze and with this approach you'll be much better that the accepted answer on large tables, as you save the NESTED LOOPS and row by row delete on nearly 1/2 of the table - only one FULL TABLE SCAN will be done.

Collectives™ on Stack Overflow

DELETE query with Common Table Expression ("WITH AS " clause)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related