SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with delete query

    How do I delete all duplicate names except 1st entry where name is made up of FIRST_NAME (VARCHAR) and LAST_NAME (VARCHAR) from a table named ATTENDENCE?

    And as an additional query how would I do this for only duplicate entries made within a 24 hour period if the row also contains a field named ENTRY_DATE (DATETIME)?

    Thanks.

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do u have an id column? If yes, something like this maight work:

    DELETE * FROM table WHERE first_name='name' AND last_name='lastname' AND id NOT IN (SELECT id FROM table WHERE first_name='name' AND last_name='lastname' LIMIT 0,1)

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    what is the primary key of the table?

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, yes I have a primary auto_increment key.

    msti, I need to do this without entering specific values otherwise it would take a long time, however you have given me an idea with NOT IN which I was unfamiliar with.

    How about:

    DELETE FROM table WHERE id NOT IN (SELECT id FROM table GROUP BY first_name, last_name)

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Code.Warrior View Post
    DELETE FROM table WHERE id NOT IN (SELECT id FROM table GROUP BY first_name, last_name)
    Does that look right? If so how do I go about including only duplicates made with 24 hour period?

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    does it have to be a 24 hour period? or can it be once per day? if once per day, then it's really easy to delete the duplicates, plus prevent duplicates in the future.

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    does it have to be a 24 hour period? or can it be once per day? if once per day, then it's really easy to delete the duplicates, plus prevent duplicates in the future.
    Hi longneck, yes I could use an index to prevent duplicates but unfortunately it's not that simple as they need to be recorded first and deleted afterwards (if not required), plus it's just got slightly more complicated!

    Table: Attendance
    Fields: First_name, Last_name, visit_date, status (default = 0)

    I'm trying to work out the query that will:

    Delete all duplicate records* leaving only the first occurance OR any that have a status of 1
    * defined as records with same first_name, last_name and visit_date within a 24 hour period.

    Hope that makes sense.

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, so a unique index is out, but i still need to know once per day vs. 24 hour period.

  9. #9
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    ok, so a unique index is out, but i still need to know once per day vs. 24 hour period.
    Yes 24 hour period, so that 23:59 and 00:01 could still be regarded as duplicate.

  10. #10
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any ideas? What about if I ignore the last visit time period all together, how should I structure my delete query?

  11. #11
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Happy New Year.

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    man, this problem is keeping me awake at night.

    any chance you can post or email me a sample data file? send me a PM and i'll give you my email address.

  13. #13
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Totally forgot about his thread. Hope it's not still keeping you up at night longneck


    Here's some sample data:

    1, 'Josh', 'Brolin', '1997-05-23 09:15:28', 0
    2, 'Jeff', 'Cohen', '1997-05-23 09:15:56', 0
    3, 'Kerri', 'Green', '1997-05-23 11:51:02', 0
    4, 'Corey', 'Feldman', '1997-05-23 13:23:26', 1
    5, 'Jeff', 'Cohen', '1997-05-24 01:04:08', 0
    6, 'Josh', 'Brolin', '1997-05-24 07:08:26', 1
    7, 'Kerri', 'Green', '1997-05-24 11:59:44', 0
    8, 'Josh', 'Brolin', '1997-05-24 08:10:33', 0

    The query should return:

    2, 'Jeff', 'Cohen', '1997-05-23 09:15:28', 0
    3, 'Kerri', 'Green', '1997-05-23 11:51:02', 0
    4, 'Corey', 'Feldman', '1997-05-23 13:23:26', 1
    6, 'Josh', 'Brolin', '1997-05-24 07:08:26', 1
    7, 'Kerri', 'Green', '1997-05-24 11:59:44', 0


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •