CODEWITHSUNDEEP
phpmysqlsqlmagentomagento-1.7
user2171083
user2171083

Reputation: 71

Magento - Delete selected products with SQL query

I have about 800,000 products in my magento database, and I need to delete about half of those products (approx 400,000).

Running on magento 1.7.0.2

It's just taking me forever to do it from the "Manage Products" page. The process takes to long and the server keeps timing out.

Is there a SQL statement that can deleted selected products from within the database? All the products that need to be deleted, the title starts with *NLA

I know magneto stores the product data in several different tables, so I'm trying to figure out how to delete all the data associated with the selected products from all tables.

I know MySQL statements, but I'm not an expert, and can't figure this one out. For example if it was only one table I would do something like this: DELETE FROM product_table WHERE title_value LIKE '*NLA%'

I would appreciate it if someone could point me in the right direction.

Upvotes: 0

Views: 4602

Answers (3)

Oscprofessionals
Oscprofessionals

Reputation: 2174

In Magento product details are stored in data base using EAV concept so there are more then 30 tables where a product data will go.

I suggest you to use Magento import export facility for bulk delete.

refer this link : magento: bulk delete products via import/export facility?

There is an extension Magmi which can be used as well.

Upvotes: 0

Yaroslav Voronoy
Yaroslav Voronoy

Reputation: 241

You can use this query

DELETE FROM product_table WHERE title_value LIKE '*NLA%

Magento used InnoDB engine Storage for MySQL with foreign keys. All data which has FK keys will be also deleted.

Upvotes: 1

mavili
mavili

Reputation: 3424

Your SQL statement should do the job IF you're deleting from a single table.

If stored on different tables, it depends on the way they are stored. If, for example, products are stored according to their key being a foreign key in the different tables, then all related tables will update when the products are deleted from the main table (depending on whether the foreign keys are set to CASCADE ON DELETE or not).

But be careful about the regex given in LIKE condition, make sure there are no other products that have 'NLA' somewhere in their title.

Edit: you said starting with 'NAL'? In that case your regex should be something like 'NAL*%'

DELETE FROM product_table WHERE title LIKE 'NAL*%'

Upvotes: 0

Related Questions