Delete Rows brought back from a Select statement that contains a having and group by clause in Oracle

Question

I have this select statement:

SELECT d.idcprefix,
         d.orgdept,
         d.idcseq,
         d.subcont, 
         d.actualcomp, 
         COUNT (*) AS "No Duplicates"
    FROM DCS_IDC, DCS_IDC z
   WHERE     D.IDCPREFIX = z.idcprefix
         AND z.orgdept = d.orgdept
         AND z.idcseq = d.idcseq
         and D.SUBCONT is not null
  HAVING COUNT (*) > 1
GROUP BY d.idcprefix,
         d.orgdept,
         d.idcseq,
         d.subcont,
         d.actualcomp
ORDER BY d.idcprefix,
         d.orgdept,
         d.idcseq,
         d.subcont,
         d.actualcomp

and I want to delete the rows that are brought back from this statement, any ideas?

A Delete FROM gives me a ORA-01732: data manipulation operation not legal on this view error because, I believe, I am comparing two tables albeit the same table.

I also tried a delete from tablename where rows exist, but that deleted far more rows than I wanted.

The "duplicates" are based on Subcont. There were records inserted into the database / application using the subcont field, which was meant to be blank, so now I have records which are the same except for one has, for example, A in subcont and it's blank in the other:

idcprefix, subcont, orgdept and idcseq
1          A        ABC         1
1                   ABC         1
2          A        BCD         1
2                   BCD         1

The query shows all the records that have a subcont that are dupliates of the ones that don't and I need to remove the ones with the subcont value.

Help would be much appreciated!

Am I correct in my understanding that this query finds duplicates on DCS_IDC, and you want to delete all but one of the duplicates? Is there a primary key on this table? Do you care which of the duplicates is retained? — Bob Jarvis - Слава Україні, Jul 24 '13 at 11:51
Hi, there is no primary key used on this table. The table uses a unique key type contstraing on idcprefix, subcont, orgdept and idcseq. I have added a bit above for context — iandavidson1982, Jul 24 '13 at 11:59

Yiannis Nennes · Accepted Answer · 2013-07-24T12:01:36.227

0

Assuming that (idcprefix, subcont, orgdept, idcseq) is unique, how about the below?

DELETE FROM DCS_IDC
WHERE (idcprefix, subcont, orgdept, idcseq) IN (
  WITH del AS (
  SELECT d.idcprefix,
     d.orgdept,
     d.idcseq,
     d.subcont, 
     d.actualcomp, 
     COUNT (*) AS "No Duplicates"
  FROM DCS_IDC, DCS_IDC z
  WHERE     D.IDCPREFIX = z.idcprefix
     AND z.orgdept = d.orgdept
     AND z.idcseq = d.idcseq
     and D.SUBCONT is not null
  HAVING COUNT (*) > 1
  GROUP BY d.idcprefix,
     d.orgdept,
     d.idcseq,
     d.subcont,
     d.actualcomp
  ORDER BY d.idcprefix,
     d.orgdept,
     d.idcseq,
     d.subcont,
     d.actualcomp
  )
 SELECT idcprefix, subcont, orgdept, idcseq FROM del
)

edited Jul 24 '13 at 12:01

answered Jul 24 '13 at 11:30

Yiannis Nennes

384
2
4

Hi, there is no primary key used on this table. The table uses a unique key type contstraing on idcprefix, subcont, orgdept and idcseq. – iandavidson1982 Jul 24 '13 at 11:54
1

Thanks Loannis, that has worked a treat! (You were missing a D after the first DCS_IDC in the FROM statement which I added and it worked perfectly after that!) – iandavidson1982 Jul 24 '13 at 12:48

Bob Jarvis - Слава Україні · Answer 2 · 2013-07-24T14:41:38.347

0

How about:

BEGIN
  FOR aRow IN (SELECT d.idcprefix,
                      d.orgdept,
                      d.idcseq,
                      COUNT(*) AS DUP_COUNT
                 FROM DCS_IDC d
                 HAVING COUNT(*) > 1
                 GROUP BY d.idcprefix,
                          d.orgdept,
                          d.idcseq)
  LOOP
    DELETE FROM DCS_IDC
      WHERE IDCPREFIX = aRow.IDCPREFIX AND
            ORGDEPT = aRow.ORGDEPT AND
            IDCSEQ = aRow.IDCSEQ AND
            SUBCONT IS NOT NULL;
  END LOOP;
END;

No guarantees. Use at your own risk. Not tested on animals - you'll be first!

Share and enjoy.

edited Jul 24 '13 at 14:41

answered Jul 24 '13 at 12:10

Bob Jarvis - Слава Україні

48,992
9
77
110

Thanks Bob, I gave that a try but was getting an FROM keyword not found where expected error. I gave Loannis answer a whirl and that has deleted the required fields. – iandavidson1982 Jul 24 '13 at 12:49
There was a comma missing after `d.idcseq` in the SELECT. Fixed now, for what it's worth. – Bob Jarvis - Слава Україні Jul 24 '13 at 14:42

Delete Rows brought back from a Select statement that contains a having and group by clause in Oracle

2 Answers2