How to delete mysql rows using join?

Question

I have three tables:

"products" -         contains products
"location" -         store location
"product_orders" -   joins data from products and location

SQL DUMP:

--
-- Table structure for table `location`
--

CREATE TABLE IF NOT EXISTS `location` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=77 ;

--
-- Dumping data for table `location`
--

INSERT INTO `location` (`id`, `name`) VALUES
(1, 'Miami'),
(2, 'Denver');

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL DEFAULT '',
  `product` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6014552 ;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `type`, `product`) VALUES
(1, 'shirt', 'red shirt'),
(2, 'shirt', 'red shirt'),
(3, 'pants', 'blue pants'),
(4, 'pants', 'blue pants');

-- --------------------------------------------------------

--
-- Table structure for table `product_orders`
--

CREATE TABLE IF NOT EXISTS `product_orders` (
  `product_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `status` varchar(255) NOT NULL,
  PRIMARY KEY (`product_id`,`location_id`),
  KEY `ix_product_orders` (`location_id`),
  KEY `ix_product_orders_1` (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `product_orders`
--

INSERT INTO `product_orders` (`product_id`, `location_id`, `status`) VALUES
(1, 1, 'sold'),
(2, 2, 'sold'),
(1, 2, 'available'),
(2, 1, 'sold');

If I want to select all sold products from store 'miami - this query works:

SELECT * FROM `products`
INNER JOIN `product_orders`
ON `product_orders`.`product_id`= `products`.`id`
WHERE `product_orders`.`location_id` = '1'
AND
status = 'sold'

How would you rewrite to delete all sold products from the miami store? My query is not working:

 DELETE `products` FROM `products`
INNER JOIN `product_orders`
ON `product_orders`.`location_id` = `products`.`id`
WHERE `product_orders`.`location_id` = '2'
AND
status = 'sold'

What I meant that, something is a miss in your provided fields, it could be, prod_orders.prodid = prod.id and prod_orders.loc = 'miami' ;-) ? — bonCodigo
– bonCodigo, Commented Dec 6, 2012 at 16:11
I was thinking the same thin, @bonCodigo ... but hey, i learned not to expect consistency from people — amphibient
– amphibient, Commented Dec 6, 2012 at 16:13
@foampile +1 for that ;-) but I bet if you two guys didn't initiate 'answering' , this q would have been self disappeared ;-) and guess why? happened to me many times after posting answer as a comment. After that I too learnt. — bonCodigo
– bonCodigo, Commented Dec 6, 2012 at 17:50

amphibient · Accepted Answer · 2012-12-06 15:59:05Z

1

DELETE FROM products WHERE id IN (SELECT location_id FROM products_orders WHERE location = 'miami' AND status = 'sold');

answered Dec 6, 2012 at 15:59

amphibient

31.7k56 gold badges159 silver badges254 bronze badges

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

Comments

Muthu Kumaran · Accepted Answer · 2012-12-06 15:58:48Z

1

Try this one,

DELETE `products` FROM `products` INNER JOIN `products_orders` 
ON `products_orders`.`location_id` = `products`.`id` 
WHERE `products_orders`.`location` = 'miami' 
AND
status = 'sold'

OR

DELETE prod FROM `products` AS prod INNER JOIN `products_orders` 
ON `products_orders`.`location_id` = `products`.`id` 
WHERE `products_orders`.`location` = 'miami' 
AND
status = 'sold'

answered Dec 6, 2012 at 15:58

Muthu Kumaran

18k5 gold badges50 silver badges72 bronze badges

Comments

bonCodigo · Accepted Answer · 2012-12-06 16:26:43Z

I am not sure how you have set foreign keys in your tables. But looking at it with the most primary assumptions, product table holds products data and location holds location data. In your common table product orders you are having one foreign key to products table by product id and one another relation to location table by location.

So to back my comment, I am posting the following query. It is your query with a change to a field that I ASSUME WAS A TYPO....in your query. :-) Again as foampile said, I might be doing it with consistency...

 DELETE FROM `products`
 INNER JOIN `products_orders` 
 ON `products_orders`.`product_id` = `products`.`id`
 WHERE `products_orders`.`location` = 'miami' 
 AND `products_orders`.status = 'sold'
 ;

Collectives™ on Stack Overflow

How to delete mysql rows using join?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related