How to do WITH AS in INSERT MySQL query?

Question

I am running a Mysql procedure (MySQL 5.7) with following query :

DECLARE LAST_MAC BIGINT;

SET LAST_MAC = (SELECT COALESCE(MAX(MAC_ADDRESS), 0) FROM MAC_ADDRESS_TABLE) + 1;
      INSERT INTO MAC_ADDRESS_TABLE(MAC_ADDRESS)
      WITH TEMP (N)
      AS (
        VALUES (LAST_MAC)
        UNION ALL
        SELECT N+1 FROM TEMP WHERE N+1 < (LAST_MAC + CREATED)
      ) SELECT N AS MAC_ADDRESS FROM TEMP;

After running the query , i am getting following error :

right syntax to use near 'TEMP (N)
      AS (
        VALUES (LAST_MAC)
        UNION ALL
        SELECT N'

I have verified this query on DB2 and PostgreSQL . It is working fine . I want to know whether WITH AS clause is not supporting in MySQL procedure query ? How can i achieve this with any alternative methods ?

try to update TEMP (N) to TEMP N and check it once – K.B Nov 30 '17 at 10:54 — K.B, Nov 30 '17 at 10:54

score 1 · Answer 1 · answered Nov 30 '17 at 10:59

1

MySql does not support WITH clause. For more information about this refer the this How do you use the "WITH" clause in MySQL?

answered Nov 30 '17 at 10:59

Jinesh Shah

922
10
18

How to do WITH AS in INSERT MySQL query?

1 Answers1