Duplicate delete condition [message #682173] |
Wed, 07 October 2020 12:16 |
|
ritmo2k
Messages: 11 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 #682192 is a reply to message #682173] |
Thu, 08 October 2020 06:29 |
Solomon Yakobson
Messages: 3286 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.
|
|
|
|
|