SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    153
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MYSQL query, search for "latest" duplicates

    I have just imported 1000 products into a shopping cart which had 3000 products. About 100 of these newly imported products are duplicates of the old ones. I found out how to find these duplicates with the following query-

    select *
    from CubeCart_inventory
    group by productCode
    having count(*) > 1

    My problem now, is that the query above lists only one of the duplicates, and that is the older one, the one that was already in the system. The import I did didnt have the same quality of data for each product as the earlier data so I want to be able to remove the duplicate that I imported today... if you get what I mean?

    So how would I do the following??-
    There is another column called ProductId and thats a sequential number, the products I entered today started at about 3000 and went up to 4000 as the value for ProductId. How can I add that to the query so it shows duplicates with
    productId > 3000

    Seems to me this would be the best way to do it and I can manually delete the results from this query from there.

    Thanks for your help.
    Last edited by chargin8; Mar 11, 2008 at 20:45.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT MAX(ProductId)
    FROM CubeCart_inventory
    GROUP BY productCode
    HAVING COUNT(*) > 1

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    153
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Dan, thanks for the reply.
    I tried the query you suggested but it didnt give me a typical list Im used to, with every column listed, all it listed was the productId column?

    IE

    MAX( ProductId )
    5434
    3355
    3372
    3413
    3416
    3421
    etc
    etc

    I was wanting something like

    select *
    from CubeCart_inventory
    group by productCode
    having count(*) > 1
    having productId > 3000


    I realise that last line is wrong but you get the idea?

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You would need to join the table on itself or add a subquery to get the other columns that match the MAX(ProductId) for each productCode.

    But you said you're simply doing this to delete those duplicates. You don't need anything other than these IDs, which are the highest ProductId value for any productCode having more than one row.

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    153
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aha I see what you mean.
    I was kinda hoping Id be able to see the entire row and delete them manually by ticking the box and hitting delete. Just so I was sure I wasnt removing the wrong things.

  6. #6
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    153
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, if Im happy with the reults from your query, how do I get to delete the results? I guess an extra line on the query may do that?
    Sorry but Im not much up with Mysql incase you didnt guess...

  7. #7
    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)
    it's not easy to delete using that statement directly because mysql doesn't allow you to use a subquery in a delete query referring to the table you're deleting from.

  8. #8
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    153
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see.
    So what can I do with the results, all it shows me is a column of data, theres none of the normal things Im used to seeing with a query, ie the X icon which enables deletion of a row?

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Do an INSERT INTO...SELECT to put the results into a temporary table, then use that table to construct the DELETE query.

  10. #10
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    153
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm Im not too sure about how that works, Ill maybe have to find a tutorial or something perhaps.

  11. #11
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    153
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK Im stumped, if someone could give me a rundown on how I would do this
    "INSERT INTO...SELECT " I would be very appreciative.

    I tried running the query and clicking insert.. but that seems a dead end, all the tuts I found were about inserting data into an empty DB, not from a query.

    Do I have to export the results of this query first perhaps or something??

  12. #12
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Three queries. First, create a temporary table to hold the IDs you're going to delete:

    Code:
    CREATE TABLE temp_ids (id int)
    Then populate that table from the query we came up with before:

    Code:
    INSERT INTO temp_ids (id) 
    SELECT MAX(ProductId)
    FROM CubeCart_inventory
    GROUP BY productCode
    HAVING COUNT(*) > 1
    Then use that temporary table to identify what ProductIds to delete from your inventory table:

    Code:
    DELETE FROM CubeCart_inventory WHERE ProductId IN (SELECT id FROM temp_ids)
    Now you can drop the temporary table. I'd also suggest making a backup of your data (export the table) just in case you make a mistake along the way.

  13. #13
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    153
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot Dan Im about to dive in, backing up first of course

  14. #14
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    153
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm ok well it seems to have deleted both the old and the new data, Im not 100% on that yet so I want to restore my backup localhost[1].sql.zip and do a check.
    I tried the import but dacoucc already exists.
    So do I do a
    drop database [dacoucc];
    to delete it first? or is there some other way to overwrite it?
    Sorry for all the questions but Im in unchartered territory here and all my searches on restoring didnt really help much, such as here
    http://dev.mysql.com/doc/refman/5.0/...-recovery.html
    Doesnt actually say how to restore from a local zip file.

  15. #15
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It depends on what you backed up when you created that file. Was it an entire database or just some tables?

    There's no way that set of queries would delete more than the IDs we identified previously, though.

  16. #16
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    153
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yup, I think you are right, it did exactly as I requested Im pretty sure, I was just a little confused.

    But for future reference, Id like to know how to do a restore?
    I backed up the entire DB, via the export funtion, still unsure how to overwrite the current DB.

  17. #17
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I don't know whether phpMyAdmin writes drop/create database queries in the file when it does an export. You can look and find out. To restore from your file, use the upload box on the SQL tab, right? Been a while since I used that program. I'd unzip it and do it at a console:

    Code:
    unzip filename.sql.zip
    mysql -uusername -ppassword --database dbname < filename.sql


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
  •