I’m trying to do something that I’m almost positive works in Postgresql:
x
WITH cte AS (SELECT something FROM something WHERE something.name='reference')
INSERT INTO somewhere (id, name)
SELECT cte.id, 'John'
UNION ALL
SELECT cte.id, 'Frank'
UNION ALL
SELECT cte.id, 'Thomas'
;
In other words, I want to grab the “something” row to be used for a bunch of new rows in “somwhere”. Each new entry should have a reference (like foreign key value) back to the same “something” row in the cte, mostly just to avoid a mess. However, MySQL is giving me that “blank stare” syntax error mechanism. Does MySQL allow WITH ... INSERT ...;
?
Here’s the actual code; it’s just setting up test data:
WITH
pr AS (SELECT pr_id from property AS p WHERE p.pr_name = 'Royal Oaks Plaza'),
u AS (SELECT pr.pr_id AS pr_id, utyp.utyp_name AS utyp_name, utyp.utyp_id AS utyp_id FROM unit_type AS utyp, pr)
INSERT INTO unit (pr_id, unt_number, utyp_id, unt_size)
SELECT pr_id, '100', utyp_id, FLOOR(500 + RAND() * 2000)
FROM u WHERE utyp_name = 'BEAUTY'
UNION ALL
SELECT pr_id, '110', utyp_id, FLOOR(500 + RAND() * 2000)
FROM u WHERE utyp_name = 'RESTAURANT'
;
Advertisement
Answer
In MySQL the INSERT
goes before the CTE
definition, i.e.
INSERT INTO somewhere (id, name)
WITH cte AS (SELECT id FROM something WHERE something.name='reference')
SELECT cte.id, 'John' FROM cte
UNION ALL
SELECT cte.id, 'Frank' FROM cte
UNION ALL
SELECT cte.id, 'Thomas' FROM cte;
So I think the following would work:
INSERT INTO unit (pr_id, unt_number, utyp_id, unt_size)
WITH
pr AS (SELECT pr_id from property AS p WHERE p.pr_name = 'Royal Oaks Plaza'),
u AS (SELECT pr.pr_id AS pr_id, utyp.utyp_name AS utyp_name, utyp.utyp_id AS utyp_id FROM unit_type AS utyp, pr)
SELECT pr_id, '100', utyp_id, FLOOR(500 + RAND() * 2000)
FROM u WHERE utyp_name = 'BEAUTY'
UNION ALL
SELECT pr_id, '110', utyp_id, FLOOR(500 + RAND() * 2000)
FROM u WHERE utyp_name = 'RESTAURANT'
;