[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

PHP MYSQL compare and delete

Hello,

I have 3 columns

ID WEIGHT SKU

I have duplicates of the SKUs with dif weights

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

ie

ID WEIGHT SKU
1   2.0         654978987
45 2.2          654978987


should delete row 1

Thanks,
Matt
0
movieprodw
Asked:
movieprodw
2 Solutions
 
Tyler LaczkoCommented:
Start with:

DELETE FROM table_name WHERE ID = (SELECT ID FROM table_name WHERE ...)
0
 
Michael701Commented:
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);
$previous_item['sku']='';
$previous_item['weight']=0;
$previous_item['id']=0;

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

0
 
rgazoniCommented:
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 EXISTS (SELECT 1 FROM temp
             WHERE temp.id = table_name.id
             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!!
0
 
movieprodwAuthor Commented:
Thanks guys
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now