我正在尝试使用“插入...在重复密钥更新功能上选择”，但我现在遇到了麻烦。
我想插入数据到‘水果属性’表。
我的查询如下：
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服务器版本相对应的手册，以获得正确的语法
我的查询有什么问题吗？我真的不知道..
谢谢.
如果使用显式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子句（yuck！！！）。但是on关键字会被混淆。
也许您可以不使用交叉连接来简化它。
不幸的是，在MySql中，CROSS JOIN只是INNER JOIN的同义词。
但您不希望最后一个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<>在此拨弄
