SELECT ANY, multi UPDATE, multi DELETE

query.sql

-- SELECT

SELECT *
  FROM orders O
  WHERE 5 <= ANY(SELECT amount FROM order_lines WHERE order_id = O.id);

SELECT *
  FROM orders O
  WHERE id IN (SELECT order_id FROM order_lines WHERE amount >= 5);

SELECT DISTINCT O.*
  FROM orders O
  JOIN order_lines L ON (L.order_id = O.id AND L.amount >= 5);

SELECT O.*, COUNT(1) AS big_lines
  FROM orders O
  JOIN order_lines L ON (L.order_id = O.id AND L.amount >= 5)
  GROUP BY L.order_id;



-- UPDATE

-- These 2 do the same thing:

UPDATE order_lines
  SET amount = amount+100
  WHERE order_id IN (select id from orders where version = 2);

UPDATE orders O, order_lines L
  SET L.amount = L.amount + 100
  WHERE O.id = L.order_id and O.version = 2;

-- These 2 update 2 tables simultaneously:

UPDATE orders O, order_lines L
  SET L.amount = L.amount + 100, O.version = 3
  WHERE O.id = L.order_id AND O.version = 2;

UPDATE order_lines L
  JOIN orders O ON (O.id = L.order_id)
  SET L.amount = L.amount + 100, O.version = 3
  WHERE O.version = 2;



-- DELETE

DELETE O, L
  FROM orders O, order_lines L
  WHERE O.id = L.order_id AND O.version = 3;

DELETE O, L
  FROM order_lines L
  JOIN orders O ON (O.id = L.order_id)
  WHERE O.version = 3;

setup.sql

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

DROP TABLE IF EXISTS `orders`;
CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer` varchar(255) NOT NULL,
  `date` date NOT NULL,
  `version` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `orders` (`id`, `customer`, `date`, `version`) VALUES
(1, 'bert', '2016-05-02', 1),
(2, 'karel', '2016-05-19', 2),
(3, 'janine', '2016-05-23', 2),
(4, 'maria', '2016-05-24', 3);

DROP TABLE IF EXISTS `order_lines`;
CREATE TABLE IF NOT EXISTS `order_lines` (
  `order_id` int(10) unsigned NOT NULL,
  `item` varchar(255) NOT NULL,
  `amount` int(10) unsigned NOT NULL,
  KEY `order_id` (`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `order_lines` (`order_id`, `item`, `amount`) VALUES
(1, 'pindakaas', 3),
(1, 'wasborstel', 1),
(1, 'pop', 1),
(2, 'pindakaas', 18),
(2, 'deurmat', 1),
(3, 'zaag', 2),
(4, 'fiets', 2),
(4, 'accu', 1);