By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,109 Members | 993 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,109 IT Pros & Developers. It's quick & easy.

compare date within the same table and delete the oldest one.

100+
P: 134
Hello everyone,

I am having the following issue:

The company for which I am working for got an MySQL table which I need to clean up. But it also contains some important data. So I need to check if an email is not unique in the table. When it is not unique in the table I need to check if it is already accepted or not. When it is accepted I need to deleted the other rows which contain the same email adress. But when multiple rows contain the same email adress and none of them got a date of acceptation. I need to check for the date on which it is created and then only the most recent one should remain in the table.

The table contains the following fields: `id`, `email`, `token`, `dtCreated`, `dtAccepted`, `networkId`, `lastInvitedBy`, `timesInvited`, `lastInvited`, `role`.

I hope I made my problem clear and thanks in advance for all the help,

Michael
Jul 16 '12 #1

✓ answered by Rabbit

Use an aggregate query to get all the max dates grouped by your unique key. Outer join that to your original table and delete any that don't match.

Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,364
Use an aggregate query to get all the max dates grouped by your unique key. Outer join that to your original table and delete any that don't match.
Jul 16 '12 #2

100+
P: 134
Thanks a lot Rabbit I really appreciate it :)
Jul 17 '12 #3

100+
P: 134
I've tried your solution but it seems I've done something wrong, because it won't work. So can you please help me again.

This is the script I've tried.
Expand|Select|Wrap|Line Numbers
  1. DELETE FROM `wv_invite`
  2. OUTER JOIN `dtCreated` ON(
  3.     SELECT `dtCreated`, MAX(`dtCreated`)
  4.         FROM `wv_invite`
  5.         WHERE `dtAccepted` IS NULL
  6.         AND `dtCreated` != `dtCreated`
And this is the error I get:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN `dtCreated` ON( SELECT `dtCreated`, MAX(`dtCreated`) FROM ' at line 2
Jul 17 '12 #4

Rabbit
Expert Mod 10K+
P: 12,364
That's not the correct syntax for a join. You also need to identify what type of outer join you want to use. It needs to be something along the lines of this:

Expand|Select|Wrap|Line Numbers
  1. DELETE FROM someTable
  2. LEFT JOIN (
  3.    your aggregate query
  4. ) AS b
  5. ON someTable.UniqueID = b.UniqueID
  6. WHERE b.UniqueID IS NULL
Jul 17 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.