PHP MYSQL compare and delete

Posted on 2011-05-02
Last Modified: 2012-05-11

I have 3 columns


I have duplicates of the SKUs with dif weights

I need to compare the SKUs and delete the smaller of the weights


1   2.0         654978987
45 2.2          654978987

should delete row 1

Question by:movieprodw
    LVL 10

    Expert Comment

    by:Tyler Laczko
    Start with:

    DELETE FROM table_name WHERE ID = (SELECT ID FROM table_name WHERE ...)
    LVL 19

    Accepted Solution

    untested code, but here's the quick method. It should get you real close'

    use the echo to test, then enable the delete command

    $sql_command = "select * from table order by sku,weight";
    $items = mysql_command ($sql_command);
    while ($this_item = mysql_fetch_assoc($items))
      if ($this_item['sku']=$previous_item['sku'])
         $sql_command = "delete from table where id=".$previous_item['id'];
    //     $ok = mysql_command (sql_command);
    echo $sql_command. "<br />\n";
      $previous_item = $this_item;

    Open in new window

    LVL 2

    Assisted Solution

    mysql doesn't accept subqueries involving the target table, which means that the following command issues an error message:
    DELETE FROM table_name A
    WHERE A.weight < (SELECT max(B.weight) FROM table_name B
                       WHERE A.sku = B.sku);

    Open in new window

    I suggest that you issue the following SQL commands:
    CREATE TABLE temp AS (SELECT * FROM table_name A
                         WHERE A.weight = (SELECT max(B.weight) FROM table_name B
                                          WHERE A.sku = B.sku));
    DELETE FROM table_name;
    INSERT INTO table_name (SELECT * FROM temp);
    DROP TABLE temp;

    Open in new window

    This should work, but beware of triggers!!! If deleting something from your table triggers some kind of constraint, you would better do the following:
    CREATE TABLE temp AS (SELECT * FROM table_name A
                         WHERE A.weight < (SELECT max(B.weight) FROM table_name B
                                          WHERE A.sku = B.sku));
    /* Notice that temp now holds records to be deleted !! */
    DELETE FROM table_name
                 WHERE =
                 AND temp.weight = table_name.weight
                 AND temp.sku = table_name.sku);
    /* i'm supposing that id is not the primary key. If if is the primary key, then you can
       drop the AND clauses */
    DROP TABLE temp;

    Open in new window

    Good luck!!
    LVL 1

    Author Closing Comment

    Thanks guys

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now