将sql与insert一起使用时出现错误1064选择重复密钥更新时

4jb9z9bj 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(285)

我正在尝试使用'插入到。。。选择复制密钥更新功能'但我现在有麻烦了。
我想将数据插入到'fruitproperty'表中。
我的问题如下：

START TRANSACTION;

SET @myVal1 := "";
SET @myVal2 := 0;
SET @myVal3 := 0;
SET @myVal4 := 0;
SET @myVal5 := 0;

SELECT masterIndex INTO @myVal1 FROM fruitMaster WHERE masterName = 'apple';
SELECT masterIndex INTO @myVal2 FROM fruitMaster WHERE masterName = 'banana';
SELECT masterIndex INTO @myVal3 FROM fruitMaster WHERE masterName = 'mango';
SELECT masterIndex INTO @myVal4 FROM fruitMaster WHERE masterName = 'melon';
SELECT masterIndex INTO @myVal5 FROM fruitMaster WHERE masterName = 'grape';

INSERT
INTO    fruitProperty
        (fruitID, masterIndex, cpValue)
SELECT  A1.fruitID, A2.masterIndex, A2.cpValue
FROM    (   
           SELECT   A.fruitID
           FROM     fruit A
                    JOIN fruitProperty B ON A.fruitID = B.fruitID
           WHERE    B.masterIndex = @myVal1 AND B.cpValue = 1
        ) A1
        CROSS JOIN
        (
           SELECT @myVal2 AS masterIndex, 1 AS cpValue
           UNION
           SELECT @myVal3, 1
           UNION
           SELECT @myVal4, 1
           UNION
           SELECT @myVal5, 1
       ) A2
ON DUPLICATE KEY UPDATE cpValue = cpValue + 1;

ROLLBACK;

我遇到了一个错误代码。
错误代码：1064您的sql语法有错误；在第21行的“key update cpvalue=1”附近，检查与您的mariadb服务器版本相对应的手册，以了解要使用的正确语法
我的问题怎么了？我真的不知道。。
谢谢您。

sql mysql mariadb

来源：https://stackoverflow.com/questions/53500165/error-1064-when-using-sql-with-insert-into-select-on-duplicate-key-updat

2条答案

按热度按时间

iyfamqjs1#

如果你使用显式的 join :

INSERT INTO fruitProperty (fruitID, masterIndex, cpValue)
SELECT f.fruitID, A2.masterIndex, A2.cpValue
FROM (SELECT f.fruitID
      FROM fruit f JOIN
           fruitProperty fp
            ON f.fruitID = fp.fruitID
      WHERE f.masterIndex = @myVal1 AND fp.cpValue = 1
     ) f JOIN
     (SELECT @myVal2 AS masterIndex, 1 AS cpValue
      UNION ALL
      SELECT @myVal3, 1
      UNION ALL
      SELECT @myVal4, 1
      UNION ALL
      SELECT @myVal5, 1
     ) A2
     ON 1=1
ON DUPLICATE KEY UPDATE cpValue = VALUES(cpValue) + 1;

我怀疑这是一个解析问题，因为mysql/mariadb支持 ON 条款 CROSS JOIN （恶心！！！）。但是 ON 关键字变得混乱。

赞(0）回复(0）举报 2021-06-18

hi3rlvi22#

也许你可以简化它而不用交叉连接。
在mysql中，交叉连接只是内部连接的同义词。
但你不想最后一次 ON 关键字作为连接的一部分被混淆。
样本数据

create table fruitMaster (masterIndex int primary key, masterName varchar(30));

insert into fruitMaster (masterIndex, masterName) values 
(1, 'apple'),(2, 'banana'),(3, 'mango'),(4, 'melon'),(5, 'grape'), (6, 'prune');

create table fruit (fruitID int primary key, fruitName varchar(30));

insert into fruit (fruitID, fruitName) values 
(10,'jonagold'),(20,'straight banana'),(40,'big melons');

create table fruitProperty (
 fruitID int, masterIndex int, cpValue int,
 primary key (fruitID, masterIndex));

insert into fruitProperty (fruitID, masterIndex, cpValue) values
(10, 1, 1),(10, 2, 1),(10, 6, 1),
(20, 2, 1),(30, 3, 1),(40, 4, 1);

插入查询

INSERT INTO fruitProperty (fruitID, masterIndex, cpValue)
SELECT F.fruitID, FM2.masterIndex, 1 AS cpValue
FROM fruit F
JOIN fruitProperty FP ON (FP.fruitID = F.fruitID AND FP.cpValue = 1)
JOIN fruitMaster FM1 ON (FM1.masterIndex = FP.masterIndex AND FM1.masterName = 'apple')
JOIN fruitMaster FM2 ON FM2.masterName IN ('banana', 'mango', 'melon', 'grape')
ON DUPLICATE KEY UPDATE cpValue = 2;

结果：

SELECT * FROM fruitProperty;

fruitID | masterIndex | cpValue
------: | ----------: | ------:
     10 |           1 |       1
     10 |           2 |       2
     10 |           3 |       1
     10 |           4 |       1
     10 |           5 |       1
     10 |           6 |       1
     20 |           2 |       1
     30 |           3 |       1
     40 |           4 |       1

db<>在这里摆弄

赞(0）回复(0）举报 2021-06-17

我来回答

将sql与insert一起使用时出现错误1064选择重复密钥更新时

2条答案

相关问题

热门标签

最新问答