need SQL help

[guest*]

While I imported my 22.000 products I choose home category as first category. The products where also linked into other categories. I use only one language. Is there possible with a SQL-command on DB to delete all productslinks from home and let them linked only in the sub-categories ?

[web.geek]

First, I must stress to do a database backup before performing any DELETE queries in your database.

Then, you could use the following query to delete category-product relationships based on the name of a category (which I assume is the most accessible value):

Version 1.4.0.7:

DELETE FROM `DBPREFIX_category_product` WHERE `id_category` = 
(SELECT `id_category` FROM `DBPREFIX_category_lang` cl
WHERE cl.`name` = 'YOUR CATEGORY NAME');

Version 1.3.1.1 seems to have the same structure in the relevant tables, so it should work there as well.

[guest*]

Thxs. I tried by this code:

DELETE FROM 'ps_category_product' WHERE 'id_category' = 1909
(SELECT 'id_category' FROM 'ps_category_lang' cl
WHERE cl.'name' = 'Varianten'); 

The subcategory where the products are is 1909 and the name Varianten. When I execute this it comes: SQL Error 1064: You have an error in your SQL Syntax; check the manual tht coressponds to your MySQL server version for the right syntax to use near 'ps_category_product' WHERE 'id_category' = 1909
(SELECT 'id_category' FROM 'ps' at line 1

If delete from all products of this category 1909 is complete, I will try to delete all products from category 'Home'.

Are you sure that it will delete only the links and not the products at all ?

I have 2 problems. Category 1909 and all the products I want to delete and the second one is to "de-link" all products in home, because these products are already linked in other shop categories.

[web.geek]

Since we are dealing with deleting data, let me make sure I understand the issues one at a time. You want to "de-link" all products from the "Home" category, correct? You can use this query to do so:

DELETE FROM `ps_category_product` WHERE `id_category` = 
(SELECT `id_category` FROM `ps_category_lang` cl WHERE cl.`name` = 'Home');

Make sure you are using the ` (backtick) character instead of the ' (apostrophe) for the column names. It's the one found to the left of the 1 key on most (US) keyboards. Use the regular apostrophe found next to the enter/return key around the literal string 'Home'.

If you use this approach (using a subquery to find the id of the category), you can re-use this query to "de-link" products from any category by replacing 'Home' with the name of another category (e.g. 'Varianten').

The ps_category_product table only links product to category, so deleting from that table only deletes the link and not the product itself.

Secondly, do you want to delete category 1909 ('Varianten') entirely along with all of the products inside it?

[guest*]

Secondly, do you want to delete category 1909 ('Varianten') entirely along with all of the products inside it?

Yes I want. These products are in double in DB and I don't need them.

Now the first code worked. Thank you very much !!!

[web.geek]

If you need to remove every trace of the products from your database, you may need to delete from as many as 21 related tables (v 1.4.0.8). What you can do to start is find and confirm the products you want to delete:

SELECT p.* FROM `ps_product_lang` p INNER JOIN `ps_category_product` cp ON p.`id_product` = cp.`id_product` WHERE `id_category` = 
(SELECT `id_category` FROM `ps_category_lang` cl WHERE cl.`name` = 'Varianten');

And make absolutely sure these are duplicate products that should be deleted and not just products that appear under multiple categories. From that, you can get the subquery for all of your deletes:

SELECT cp.`id_product` FROM `ps_category_product` cp 
INNER JOIN `ps_category_lang` cl ON cp.`id_category` = cl.`id_category` 
WHERE cl.`name` = 'Varianten';

MAKE SURE YOU HAVE A BACKUP OF YOUR DATABASE AND KNOW HOW TO RESTORE IT BEFORE PROCEEDING

A good practice is to always run a SELECT query before the delete to confirm the rows that are about to be deleted:

SELECT * FROM `YOUR_TABLE_NAME` WHERE `id_product` IN (
SELECT cp.`id_product` FROM `ps_category_product` cp 
INNER JOIN `ps_category_lang` cl ON cp.`id_category` = cl.`id_category` 
WHERE cl.`name` = 'Varianten');

Then, for each table that contains products, you would create a DELETE query similar to this:

DELETE FROM `YOUR_TABLE_NAME` WHERE `id_product` IN (
SELECT cp.`id_product` FROM `ps_category_product` cp 
INNER JOIN `ps_category_lang` cl ON cp.`id_category` = cl.`id_category` 
WHERE cl.`name` = 'Varianten');

I only have versions 1.3.1.1 and 1.4.0.x, so here are the tables that contain `id_product` in each of those versions. You would substitute these names for "YOUR_TABLE_NAME" in the queries, one at a time:

* 1.3.1.1 only
** ps_discount_quantity
* 1.4.0.x only
** ps_customer_thread
** ps_product_country_tax
** ps_product_group_reduction_cache
** ps_specific_price
** ps_stock_mvt
* both versions
** ps_attribute_impact
** ps_cart_product
** ps_customization
** ps_customization_field
** ps_feature_product
** ps_image
** ps_product_attachment
** ps_product_attribute
** ps_product_download
** ps_product_lang
** ps_product_sale
** ps_product_tag
** ps_scene_products
** ps_search_index
* both versions (delete these last)
** ps_category_product
** ps_product

These tables are product related, so you may want to look at them as well. We can address them later if necessary, because the DELETE statements will be slightly different.

* ps_accessory
* ps_pack

You did backup your database first, right? ;-)

[guest*]

Oh Jesus.
Now I understand why presta freezes a time when I'm deleting products on BO.... It was always possible to delete only 50 products each time. When I tried whith 100 than server gives me a time-out...
I think presta must be released in here... That's not a good possibility to delete products by mass...

Thank you for the code. I will save it for the future. My 3.000 products I deleted yesterday/today in stages of 50 per request on BO to get sure there will ge deleted from all tables.

[guest*]

The code worked, but the problem is that presta has also a standard category you can link. This category (home) remains linked and give some errors, when I try to export pdf f.ex., or somebody is using the search fronted. Affected are about 7.000 products from 17.000. How can I de-link this ? Or is this not possible on DB-basis only BO by choosing a new standard-category ? At any way products are linked also in other categories, so nothing will be lost. I want only to change these products to the next linked sub-category in tree as standard.

ON DB I found the products all under: ps_product, id_category_default.

The default category is not the same for all products. Each one is linked to a sepcific supplier. All supplier have a non shop-active category in the DB, so it is possible to trace them. f.ex. supplier 3 = category 1234

Version I want to de-link ist 1.3.3.