Using MySQL 8.0 WITH Clause for INSERT, UPDATE, DELETE, Recursive Queries and Data Generation

Author Yang Taotao is a senior database expert with over ten years of experience in MySQL, PostgreSQL, MongoDB and focuses on backup & recovery, SQL tuning, monitoring, high‑availability architecture, and technical training.

MySQL 8.0 introduced the WITH clause (common table expression). The article shows how WITH can be used not only with SELECT but also with INSERT, UPDATE, DELETE, recursive queries, multiple CTEs, and derived tables to simplify routine SQL tasks.

1. Using WITH to generate data

localhost:ytt>create table y1 (id serial primary key, r1 int,log_date date);
Query OK, 0 rows affected (0.09 sec)

localhost:ytt>INSERT y1 (r1,log_date)
    -> WITH recursive tmp (a, b) AS
    -> (SELECT
    ->   1,
    ->   '2021-04-20'
    -> UNION
    -> ALL
    -> SELECT
    ->   ROUND(RAND() * 10),
    ->   b - INTERVAL ROUND(RAND() * 1000) DAY
    -> FROM
    ->   tmp
    -> LIMIT 100) TABLE tmp;
Query OK, 100 rows affected (0.03 sec)
Records: 100  Duplicates: 0  Warnings: 0

localhost:ytt>table y1 limit 10;
+----+------+------------+
| id | r1   | log_date   |
+----+------+------------+
|  1 |    1 | 2021-04-20 |
|  2 |    8 | 2020-04-02 |
|  3 |    5 | 2019-05-26 |
|  4 |    1 | 2018-01-21 |
|  5 |    2 | 2016-09-08 |
|  6 |    9 | 2016-06-14 |
|  7 |    7 | 2016-02-06 |
|  8 |    6 | 2014-03-18 |
|  9 |    6 | 2011-08-25 |
| 10 |    9 | 2010-02-02 |
+----+------+------------+
10 rows in set (0.00 sec)

2. Using WITH with UPDATE

localhost:ytt>WITH recursive tmp (a, b, c) AS
    -> (SELECT
    ->   1,
    ->   1,
    ->   '2021-04-20'
    -> UNION ALL
    -> SELECT
    ->   a + 2,
    ->   100,
    ->   DATE_SUB(
    ->     CURRENT_DATE(),
    ->     INTERVAL ROUND(RAND() * 1000, 0) DAY
    ->   )
    -> FROM
    ->   tmp
    -> WHERE a < 100)
    -> UPDATE
    ->   tmp AS a,
    ->   y1 AS b
    -> SET
    ->   b.r1 = a.b
    -> WHERE a.a = b.id;
Query OK, 49 rows affected (0.02 sec)
Rows matched: 50  Changed: 49  Warnings: 0

localhost:ytt>table y1 limit 10;
+----+------+------------+
| id | r1   | log_date   |
+----+------+------------+
|  1 |    1 | 2021-04-20 |
|  2 |    8 | 2019-12-26 |
|  3 |  100 | 2018-06-12 |
|  4 |    8 | 2017-07-11 |
|  5 |  100 | 2016-08-10 |
|  6 |    9 | 2015-09-14 |
|  7 |  100 | 2014-12-19 |
|  8 |    2 | 2014-08-13 |
|  9 |  100 | 2014-08-05 |
| 10 |    8 | 2011-11-12 |
+----+------+------------+
10 rows in set (0.00 sec)

3. Using WITH with DELETE

localhost:ytt>WITH recursive tmp (a) AS
    -> (SELECT
    ->   1
    -> UNION
    -> ALL
    -> SELECT
    ->   a + 2
    -> FROM
    ->   tmp
    -> WHERE a < 100)
    -> DELETE FROM y1 WHERE id IN (TABLE tmp);
Query OK, 50 rows affected (0.02 sec)

localhost:ytt>table y1 limit 10;
+----+------+------------+
| id | r1   | log_date   |
+----+------+------------+
|  2 |    6 | 2019-05-16 |
|  4 |    8 | 2015-12-07 |
|  6 |    2 | 2014-05-14 |
|  8 |    7 | 2010-05-07 |
| 10 |    3 | 2007-03-27 |
| 12 |    6 | 2006-12-14 |
| 14 |    3 | 2004-04-22 |
| 16 |    7 | 2001-09-16 |
| 18 |    7 | 2001-01-04 |
| 20 |    7 | 2000-02-12 |
+----+------+------------+
10 rows in set (0.00 sec)

Note: When used with DELETE, the WITH expression is read‑only, so it cannot appear in a multi‑table DELETE.

localhost:ytt>WITH recursive tmp (a) AS
    ->  (SELECT
    ->    1
    ->  UNION
    ->  ALL
    ->  SELECT
    ->    a + 2
    ->  FROM
    ->    tmp
    ->  WHERE a < 100)
    ->  delete a,b from y1 a join tmp b where a.id = b.a;
ERROR 1288 (HY000): The target table b of the DELETE is not updatable

4. Nesting WITH clauses

localhost:ytt>SELECT * FROM  
    ->   (
    ->     WITH tmp1 (a, b, c) AS 
    ->     (
    ->       VALUES
    ->         ROW (1, 2, 3),
    ->         ROW (3, 4, 5),
    ->         ROW (6, 7, 8)
    ->     ) SELECT  * FROM
    ->         (
    ->           WITH tmp2 (d, e, f) AS (
    ->             VALUES
    ->               ROW (100, 200, 300),
    ->               ROW (400, 500, 600)
    ->             ) TABLE tmp2
    ->         ) X
    ->           JOIN tmp1 Y
    ->   ) Z ORDER BY a;
+-----+-----+-----+---+---+---+
| d   | e   | f   | a | b | c |
+-----+-----+-----+---+---+---+
| 400 | 500 | 600 | 1 | 2 | 3 |
| 100 | 200 | 300 | 1 | 2 | 3 |
| 400 | 500 | 600 | 3 | 4 | 5 |
| 100 | 200 | 300 | 3 | 4 | 5 |
| 400 | 500 | 600 | 6 | 7 | 8 |
| 100 | 200 | 300 | 6 | 7 | 8 |
+-----+-----+-----+---+---+---+
6 rows in set (0.01 sec)

5. Multiple CTEs joined together

localhost:ytt>WITH 
    -> tmp1 (a, b, c) AS 
    -> (
    -> VALUES
    -> ROW (1, 2, 3),
    -> ROW (3, 4, 5),
    -> ROW (6, 7, 8)
    -> ),
    -> tmp2 (d, e, f) AS (
    ->     VALUES
    ->       ROW (100, 200, 300),
    ->       ROW (400, 500, 600)
    -> )
    -> SELECT * FROM  tmp2,tmp1 ORDER BY a;
+-----+-----+-----+---+---+---+
| d   | e   | f   | a | b | c |
+-----+-----+-----+---+---+---+
| 400 | 500 | 600 | 1 | 2 | 3 |
| 100 | 200 | 300 | 1 | 2 | 3 |
| 400 | 500 | 600 | 3 | 4 | 5 |
| 100 | 200 | 300 | 3 | 4 | 5 |
| 400 | 500 | 600 | 6 | 7 | 8 |
| 100 | 200 | 300 | 6 | 7 | 8 |
+-----+-----+-----+---+---+---+
6 rows in set (0.00 sec)

6. Generating a date series with WITH

localhost:ytt>WITH recursive seq_date (log_date) AS
    ->      (SELECT
    ->        '2020-01-01'
    ->      UNION
    ->      ALL
    ->      SELECT
    ->        log_date + INTERVAL 1 DAY
    ->      FROM
    ->        seq_date
    ->      WHERE log_date + INTERVAL 1 DAY < '2020-02-01')
    ->      SELECT
    ->        log_date
    ->      FROM
    ->        seq_date;
+------------+
| log_date   |
+------------+
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
| 2020-01-04 |
| 2020-01-05 |
| 2020-01-06 |
| 2020-01-07 |
| 2020-01-08 |
| 2020-01-09 |
| 2020-01-10 |
| 2020-01-11 |
| 2020-01-12 |
| 2020-01-13 |
| 2020-01-14 |
| 2020-01-15 |
| 2020-01-16 |
| 2020-01-17 |
| 2020-01-18 |
| 2020-01-19 |
| 2020-01-20 |
| 2020-01-21 |
| 2020-01-22 |
| 2020-01-23 |
| 2020-01-24 |
| 2020-01-25 |
| 2020-01-26 |
| 2020-01-27 |
| 2020-01-28 |
| 2020-01-29 |
| 2020-01-30 |
| 2020-01-31 |
+------------+
31 rows in set (0.00 sec)

7. Using WITH as a derived table

localhost:ytt>SELECT
    ->        *
    ->      FROM
    ->        (
    ->          WITH recursive seq_date (log_date) AS
    ->          (SELECT
    ->            '2020-01-01'
    ->          UNION
    ->          ALL
    ->          SELECT
    ->            log_date + INTERVAL 1 DAY
    ->          FROM
    ->            seq_date
    ->          WHERE log_date+ interval 1 day  < '2020-02-01')
    ->  select * 
    ->          FROM
    ->            seq_date
    ->          ) X
    ->          LIMIT 10;
+------------+
| log_date   |
+------------+
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
| 2020-01-04 |
| 2020-01-05 |
| 2020-01-06 |
| 2020-01-07 |
| 2020-01-08 |
| 2020-01-09 |
| 2020-01-10 |
+------------+
10 rows in set (0.00 sec)

The WITH expression is highly flexible; different scenarios allow various syntaxes, and it can effectively simplify routine SQL development.