mysql - 如何使用 join 删除 mysql 行？

Question

我有三个表:

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

SQL 转储:

--
-- 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');

如果我想从“miami”商店中选择所有已售出的产品 - 此查询有效:

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

您将如何重写以删除迈阿密商店中所有已售出的产品？我的查询不起作用:

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

Answer 1

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