Skip to content

Instantly share code, notes, and snippets.

@rudiedirkx rudiedirkx/query.sql
Created May 24, 2016

Embed
What would you like to do?
SELECT ANY, multi UPDATE, multi DELETE
-- 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;
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.