Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate delete condition (11.2)
Duplicate delete condition [message #682173] Wed, 07 October 2020 12:16 Go to next message
ritmo2k
Messages: 4
Registered: October 2020
Junior Member
I have a table, userattr with several million rows of attribute name and value data for users.

Some of those attribute names are constrained to only allow for a maximum of one entry per user. This constraint is known in the attrdef table.

Due to an application bug, we are seeing multiple entries per user for attributes which are constrained to at most one entry.

I am using the following abysmally inefficient query to identify and remove all but one (we don't care which remains).


DELETE FROM "userattr"
WHERE ("userid","attrkey","seqno") IN
(
  WITH candidates AS
  (
    SELECT u.*, COUNT(*) OVER (PARTITION BY "userid","attrkey") ct
    FROM "userattr" u
    WHERE u."attrkey" IN (SELECT "id" FROM "attrdef" WHERE "maxvalues" = 1)
  )
  SELECT "userid","attrkey","seqno"
  FROM candidates
  WHERE ct > 1
)
AND "seqno" NOT IN (
  SELECT MAX("seqno")
  FROM
  (
    WITH candidates AS
    (
      SELECT u.*, COUNT(*) OVER (PARTITION BY "userid","attrkey") ct
      FROM "userattr" u
      WHERE u."attrkey" IN (SELECT "id" FROM "attrdef" WHERE "maxvalues" = 1)
    )
    SELECT *
    FROM candidates
    WHERE ct > 1
  )
  GROUP BY "userid","attrkey"
);
How can I identify the result set and delete it more elegantly and without repetition?

Thank you.
Re: Duplicate delete condition [message #682178 is a reply to message #682173] Thu, 08 October 2020 01:23 Go to previous messageGo to next message
John Watson
Messages: 8426
Registered: January 2010
Location: Global Village
Senior Member
Are you saying that "userid","attrkey","seqno" should be a unique key?
Re: Duplicate delete condition [message #682188 is a reply to message #682178] Thu, 08 October 2020 05:46 Go to previous messageGo to next message
ritmo2k
Messages: 4
Registered: October 2020
Junior Member
Hello,
Yes, the "seqno" column is unique table wide, so for a given "userid" (and "attrkey"), the combination is also unique.
Re: Duplicate delete condition [message #682191 is a reply to message #682178] Thu, 08 October 2020 06:12 Go to previous messageGo to next message
John Watson
Messages: 8426
Registered: January 2010
Location: Global Village
Senior Member
So are you saying that "seqno" is a surrogate key and that "userid","attrkey" should be a natural key?
Re: Duplicate delete condition [message #682192 is a reply to message #682173] Thu, 08 October 2020 06:29 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3007
Registered: January 2010
Location: Connecticut, USA
Senior Member
DELETE "userattr" u1
 WHERE u1."seqno" < (
                     SELECT  MAX(u2."seqno")
                       FROM  "userattr" u2
                       WHERE u2."userid" = u1."userid"
                         AND u2."attrkey" = u1."attrkey"
                         AND u2."attrkey" IN (
                                              SELECT  "id"
                                                FROM  "attrdef"
                                                WHERE "maxvalues" = 1
                                             )
                       HAVING COUNT(*) > 1
                    )
/
SY.
Re: Duplicate delete condition [message #682193 is a reply to message #682191] Thu, 08 October 2020 07:32 Go to previous messageGo to next message
ritmo2k
Messages: 4
Registered: October 2020
Junior Member
Hi, "seqno" is just a sequence, each time a sproc inserts a record, it calls ThatTablesSequence.NEXTVALUE in the statement. Effectively, it is auto incrementing integral column.

I don't know why it was designed that way and did not use an auto incrementing column, that exceeds my knowledge.
Re: Duplicate delete condition [message #682195 is a reply to message #682192] Thu, 08 October 2020 07:46 Go to previous message
ritmo2k
Messages: 4
Registered: October 2020
Junior Member
That's awesome, thank you everyone for the help!
Previous Topic: OBJECT TYPE Body creation ERROR
Next Topic: Improve efficiency of script
Goto Forum:
  


Current Time: Sat Nov 28 23:21:53 CST 2020