从oscommerce中删除非活动产品

Question

i am using latest oscommerce.

I got a huge amount of inactive products.I want to remove them.Going though admin one at a time is really slow.

I thought if i create a new temp category and move all inactive products to this temp category then using back end of oscommerce i can easily delete them.Doing this will also remove the associated image.

Products are associated via product id and categories association is done by product to category table. inactive products are set via products_status = 0;

CREATE TABLE IF NOT EXISTS `products` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `products_quantity` int(4) NOT NULL,
  `products_model` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_ean` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `google_product_category` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_price` decimal(15,4) NOT NULL,
  `products_date_added` datetime NOT NULL,
  `products_last_modified` datetime DEFAULT NULL,
  `products_date_available` datetime DEFAULT NULL,
  `products_weight` decimal(5,2) NOT NULL,
  `products_status` tinyint(1) NOT NULL,
  `products_tax_class_id` int(11) NOT NULL,
  `manufacturers_id` int(11) DEFAULT NULL,
  `products_ordered` int(11) NOT NULL DEFAULT '0',
  `products_last_import` datetime DEFAULT NULL,
  `products_submit_google` smallint(6) NOT NULL DEFAULT '1',
  `icecat_prodid` int(10) unsigned NOT NULL,
  `vendors_id` int(11) DEFAULT '1',
  `products_availability` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`products_id`),
  KEY `idx_products_model` (`products_model`),
  KEY `idx_products_date_added` (`products_date_added`),
  KEY `idx_icecat_prodid` (`icecat_prodid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=292067 ;


CREATE TABLE IF NOT EXISTS `products_to_categories` (
  `products_id` int(11) NOT NULL,
  `categories_id` int(11) NOT NULL,
  PRIMARY KEY (`products_id`,`categories_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

i have tried using the following query but i get an error #1062 - Duplicate entry '276917-29240' for key 'PRIMARY'

Update products p ,products_to_categories pc 
set pc.categories_id = 29598 
where p.products_id = pc.products_id  
and p.products_status = 0

Answer 1

You most likely have a product that is linked in one or more categories.

Example: Product ABC with products_id = 123 can exist twice in the products_to_categories table if it is in two categories (say 'categories_id` 222 and 333. So you have two entries in your table, 123-222 and 123-333.

When you run your update, the first time it encounters product/category 123-222, it will change it's category to 123-29598. When it encounters the product/category 123-333, it will also try to update the row to 123-29598, due to your primary key constraint, and would cause the problem you see.

Perhaps in your script you can check if the product (123) already exists in the category, and if so, then remove the second entry (123-333) rather than change it's category to (123-29598). See here for information on deleting entries with the same id from your table.