mysql cte . using with and insert it alerts a syntax error

Question

i am trying to write this query on mysql8 but it keeps telling me that the syntax is wrong. what should i do?

WITH this_year AS (
    SELECT YEAR(CURDATE())
),
max_val AS (
    SELECT 
        IFNULL(MAX(custom_id_counter), 0) 
    FROM flow_instances AS max_val
    WHERE 
        custom_id_year = YEAR(CURDATE())
)
INSERT INTO flow_instances (
    custom_id_year,
    custom_id_counter
) VALUES (
    this_year,
    max_val+1
);

error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO flow_instances ( custom_id_year,
custom_id_counter ) VALUES ( ' at line 11

Answer 1

Use insert . . . select:

INSERT INTO flow_instances (custom_id_year, custom_id_counter)
WITH this_year AS (
      SELECT YEAR(CURDATE()) as this_year
     ),
     max_val AS (
      SELECT COALESCE(MAX(custom_id_counter), 0) as max_val
      FROM flow_instances AS max_val
      WHERE custom_id_year = YEAR(CURDATE())
     )
    SELECT ty.this_year, mv.max_val + 1
    FROM this_year ty CROSS JOIN
         max_val mv;

You need to reference the CTEs in order to use the values they define.