WITH queries with INSERT in MySQL

February 9, 2022

I’m trying to do something that I’m almost positive works in Postgresql:

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

>Solution :

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;

Example on DB Fiddle

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