Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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 works only, 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:46:45 CDT