Re: Delete record with dup pkey

Mark Weghorst wrote:
>
> I could really use someones help with a problem that I've got. I've got
> some data with duplicate primary keys(not currently enabled), and I need
> to delete one of them, but I want to delete the one with the most null
> columns. For example:
>
> select * from sometable where pkey = '17109';
>
> Pkey Column1 Column2 Column3 Column4
> ===== ======= ======= ======= =======
> 17109 source1 31728 10628
> 17109 source2 10/6/96
>
> In this instance I want to keep the first record and delete the second.
> After the duplicate pkeys are elimated I can alter the table and create
> the primary key and other constraints.
>
> The big question is how on earth do I select the row with the most null
> fields for deletion given a primary key value. I've considered using the
> max() function to create 2 identical rows, but I'd really like to be able
> to just delete the one with the most nulls.
>
> Thanks in advance.
>
> -Mark Weghorst

Try:

DELETE FROM sometable s1
WHERE PKEY = '17109'
AND NOT EXISTS (
   SELECT NULL
   FROM sometable
   WHERE PKEY = s1.PKEY

   AND DECODE(Column1, null, 1, 0)
     + DECODE(Column2, null, 1, 0)
     + DECODE(Column3, null, 1, 0)
     + DECODE(Column4, null, 1, 0) >
       DECODE(s1.Column1, null, 1, 0)
     + DECODE(s1.Column2, null, 1, 0)
     + DECODE(s1.Column3, null, 1, 0)
     + DECODE(s1.Column4, null, 1, 0)

However, this only works, if there exists exactly one row with the highest number of NULL-columns for a given PKEY-value. If more such rows exist, no deletion occurs for that PKEY-value.

Marcus Reichardt Received on Tue Aug 18 1998 - 05:44:15 CDT