Delete rows in mysql using query

Question

I have two tables.

Here is the structure

CREATE TABLE IF NOT EXISTS `CATALOG_CATEGORY_PRODUCT` 
  ( 
     `CATEGORY_ID` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Category ID', 
     `PRODUCT_ID`  INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Product ID', 
     `POSITION`    INT(11) NOT NULL DEFAULT '0' COMMENT 'Position', 
     PRIMARY KEY (`CATEGORY_ID`, `PRODUCT_ID`), 
     KEY `IDX_CATALOG_CATEGORY_PRODUCT_PRODUCT_ID` (`PRODUCT_ID`) 
  ) 
ENGINE=INNODB 
DEFAULT CHARSET=UTF8 
COMMENT='Catalog Product To Category Linkage Table'; 

CREATE TABLE IF NOT EXISTS `CATALOG_PRODUCT_ENTITY_TIER_PRICE` 
  ( 
     `VALUE_ID`          INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID', 
     `ENTITY_ID`         INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 
     'Entity ID', 
     `ALL_GROUPS`        SMALLINT(5) UNSIGNED NOT NULL DEFAULT '1' COMMENT 
     'Is Applicable To All Customer Groups', 
     `CUSTOMER_GROUP_ID` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 
     'Customer Group ID', 
     `QTY`               DECIMAL(12, 4) NOT NULL DEFAULT '1.0000' COMMENT 'QTY', 
     `VALUE`             DECIMAL(12, 4) NOT NULL DEFAULT '0.0000' COMMENT 
     'Value', 
     `WEBSITE_ID`        SMALLINT(5) UNSIGNED NOT NULL COMMENT 'Website ID', 
     PRIMARY KEY (`VALUE_ID`) 
  ) 
ENGINE=INNODB 
DEFAULT CHARSET=UTF8 
COMMENT='Catalog Product Tier Price Attribute Backend Table';

I used this following query

SELECT * FROM `catalog_product_entity_tier_price` LEFT OUTER JOIN catalog_category_product ON catalog_product_entity_tier_price.entity_id = catalog_category_product.product_id WHERE catalog_category_product.category_id = 57 AND catalog_product_entity_tier_price.qty = 500.0000

It returned 97 rows. I would like to delete those 97 rows.

So I used this query.

DELETE FROM `catalog_product_entity_tier_price` LEFT OUTER JOIN catalog_category_product WHERE catalog_product_entity_tier_price.entity_id = catalog_category_product.product_id AND catalog_category_product.category_id = 57 AND catalog_product_entity_tier_price.qty = 500.0000

But i'm getting this error.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT OUTER JOIN catalog_category_product WHERE catalog_product_entity_tier_price' at line 1

Can someone tell me the correct syntax?

Thank you

Strawberry · Accepted Answer · 2013-10-25 16:36:40Z

1

Try this::

    DELETE cpet 
FROM `catalog_product_entity_tier_price` cpet 
LEFT OUTER JOIN catalog_category_product ccp
ON
cpet.entity_id = ccp.product_id 
AND ccp.category_id = 57 
WHERE cpet.qty = 500.0000

edited Oct 25, 2013 at 16:36

Strawberry

34k14 gold badges43 silver badges57 bronze badges

answered Oct 25, 2013 at 16:19

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PrivateUser Over a year ago

Thanks for your answer. I would like to delete rows only from catalog_product_entity_tier_price table. Are you sure it delete the rows only from that table?

Sashi Kant Over a year ago

@Giri: Yes, so I have referred that after delete

PrivateUser Over a year ago

I tried your code. This is the error i'm getting.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE cpet.entity_id = ccp.product_id AND ccp.category_id = 57 AND cpet.qt' at line 4

Strawberry Over a year ago

I've taken the unusual step of editing someone else's answer. I hope that's OK

Collectives™ on Stack Overflow

Delete rows in mysql using query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related