WITH queries with INSERT in MySQL-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You can try to use a subquery to do the UNION ALL because CTE only can be used in the first query.

WITH cte AS (SELECT something FROM something WHERE something.name='reference')
INSERT INTO somewhere (id, name)
SELECT id,name
FROM (
    SELECT cte.id, 'John' name
    UNION ALL
    SELECT cte.id, 'Frank'
    UNION ALL
    SELECT cte.id, 'Thomas'
) t1;

so you can do that in your query

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,utyp_id,num
    FROM (
        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) num
        FROM u WHERE utyp_name = 'RESTAURANT'
    ) t1
;

from your query I would rewrite by IN instead of UNION ALL

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, '110', utyp_id, FLOOR(500   RAND() * 2000) num
    FROM u 
    WHERE utyp_name IN ('RESTAURANT','BEAUTY')
    
;

CodePudding user response：

Proof of concept script for mySQL "INSERT WITH"

CREATE TABLE fromm field char(10));
INSERT INTO fromm VALUES ('Hello!');
CREATE TABLE too field char(10));
INSERT INTO too (field) 
WITH pre AS 
( SELECT field f FROM fromm)
SELECT f FROM pre;
SELECT field FROM too;
DROP TABLE fromm;
DROP TABLE too;