在MySQL Case中使用多个Insert语句

问题描述

我正在尝试从MySQL表中选择一个值(id)，并在更新语句中使用它-全部在MySQL查询中. select由两部分组成:如果id存在，则返回它；如果没有，则完成2次插入并返回ID.

I'm trying to select a value (id) from a MySQL table and use it in a update statement - all in a MySQL query. The select is composed of 2 parts: if the id exists, it is returned; if not, 2 inserts are done and the id is returned.

我有以下查询:

SELECT
    (CASE a.id WHEN '' THEN (
        DELIMITER //
        INSERT INTO xxxx (item_id, date_created, date_modified) VALUES (3313, NOW(), NOW())//
        INSERT INTO yyyy (item_id, locale_id, value, date_created, date_modified) VALUES(LAST_INSERT_ID(), 2, TRIM(SUBSTRING_INDEX('some text: 250 x 46 x 584', ':', 1)), NOW(), NOW())//
        SELECT c.id FROM xxxx c JOIN yyyy d WHERE c.item_id=3313 AND d.value='some text' LIMIT 1
    ) ELSE a.id END
) AS trans_ref_id
FROM xxxx a JOIN yyyy b ON a.id = b.item_id
WHERE b.value='some text'

运行它时，出现以下错误:

When i run it, i get the following error:

SQL错误(1064):您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取在')附近使用的正确语法 ELSE帮助 结尾 ) 作为trans_ref_id 从xxxx'在第2行

SQL Error (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 ') ELSE a.id END ) as trans_ref_id FROM xxxx' at line 2

我在这里使用错误的方法吗?这个错误是从哪里来的?

Am I having the wrong approach here? Where is this error coming from?

推荐答案

您的广告尝试不符合 SELECT + INSERT语法，即:

Your creative attempt does not conform to the SELECT+INSERT syntax, which is:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

这篇关于在MySQL Case中使用多个Insert语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！