Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm running a reverse auction website and I need to delete the duplicate bids and keep only the highest amount . lastPriceBid_rounded is the "amount". The duplicate is defined by the userId.

+----------------------+--------------+------+-----+-------------------+-------------------+
| Field                | Type         | Null | Key | Default           | Extra             |
+----------------------+--------------+------+-----+-------------------+-------------------+
| lastPriceBid         | varchar(125) | YES  |     | NULL              |                   |
| dateBidded           | varchar(125) | YES  |     | NULL              |                   |
| owner                | varchar(65)  | YES  |     | NULL              |                   |
| status               | int(11)      | YES  |     | 0                 |                   |
| userId               | varchar(125) | YES  |     | NULL              |                   |
| siteId               | int(11)      | YES  |     | NULL              |                   |
| lastPriceBid_rounded | int(11)      | YES  |     | NULL              |                   |
| from_nickname        | varchar(125) | YES  |     | NULL              |                   |
| id                   | int(11)      | NO   | PRI | NULL              | auto_increment    |
| time_updated         | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update         |
|                                                                        CURRENT_TIMESTAMP |
+----------------------+--------------+------+-----+-------------------+-------------------+

So far I've tried

DELETE n1 
FROM offers n1, offers n2 
WHERE n1.lastPriceBid_rounded < n2.lastPriceBid_rounded 
AND n1.userId = n2.userId;

but it doesn't seem to work well. I still have some duplicates (I see that when I use SELECT DISTINCT).

share|improve this question

1 Answer 1

Why does the DELETE Query not work ?

Whenever a table is used against itself in a query other than SELECT, rows tend to disappear during the optimization phase. I wrote about that back on Feb 22, 2011 : Problem with MySQL subquery

What can you do then ?

Create a new offers table to see what the table would look like if the DELETE were actually done:

DROP TABLE IF EXISTS offers_after_delete;
CREATE TABLE offers_after_delete LIKE offers;
INSERT INTO offers_after_delete
SELECT B.* FROM
(SELECT userId,site,MAX(lastPriceBid_rounded) highest_amount
FROM offers GROUP BY userId,site) A
INNER JOIN offers B USING (userId,site);

Please note that I based the delete operation on a distinct user per unique site. Now, browse through offers_after_delete. If the table looks correct to you, then delete like this:

DROP TABLE IF EXISTS offers_after_delete;
CREATE TABLE offers_after_delete LIKE offers;
INSERT INTO offers_after_delete
SELECT B.* FROM
(SELECT userId,site,MAX(lastPriceBid_rounded) highest_amount
FROM offers GROUP BY userId,site) A
INNER JOIN offers B USING (userId,site);
ALTER TABLE offers RENAME offers_old;
ALTER TABLE offers_after_delete RENAME offers;
DROP TABLE offers_old;

Please rehearse this on a DB Staging Server.

Give it a Try !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.