MySQL CTE。使用WITH和INSERT IT会提示语法错误

mysql cte . using with and insert it alerts a syntax error(MySQL CTE。使用WITH和INSERT IT会提示语法错误)

问题描述

我试图在mysql8上编写这个查询，但它一直告诉我语法错误。我该怎么办？

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

错误：

错误代码：1064。您的SQL语法中有一个错误；请检查 与您的MySQL服务器版本对应的手册 使用NEAR‘INSERT INSERT FLOW_INSTANCES(CUSTOM_ID_Year，
)的语法 Custom_id_Counter)值(‘在第11行

推荐答案

使用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;

您需要引用CTE才能使用它们定义的值。

这篇关于MySQL CTE。使用WITH和INSERT IT会提示语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！