可能未回答的重复问题:选择-在重复键更新中使用选定的值的插入
我无法使用select子句中的子查询结果列作为insert into ....(select... ) update on duplicate查询中要更新的值。
获取Error Code: 1054. Unknown column in 'field list'错误。
附加简化和简单的复制步骤。
我最初的案例涉及15列和太多复杂的JSON_TABLEs。我的工作是在update子句中再次重复整个子查询,但与省略该列和更新相比,这会使查询执行时间翻倍。
drop table if exists t1;
create table t1 (a int primary key auto_increment, b int);
drop table if exists t2;
create table t2 (c int primary key auto_increment, d int);
insert into t1 (a, b) values (null, 0);
insert into t1 (a, b) values (null, 1);
insert into t1 (a, b) values (null, 2);
insert into t1 (a, b) values (null, 3);
insert into t1 (a, b) values (null, 4);
insert into t1 (a, b) values (null, 5);
insert into t1 (a, b) values (null, 6);
insert into t1 (a, b) values (null, 7);
insert into t1 (a, b) values (null, 8);
insert into t1 (a, b) values (null, 9);
insert into t2 (c, d) values (null, 0);
--doesnt work
insert into t2 (c, d)
select a, (select 1) as b1 from t1
on duplicate key update
d = t1.b1;
--doesnt work
insert into t2 (c, d)
select a, (select 1) as b1 from t1
on duplicate key update
d = values(b1);
--work around that takes twice as much time
insert into t2 (c, d)
select a, (select 1) as b1 from t1
on duplicate key update
d = (select 1);发布于 2022-11-21 04:15:30
(SELECT 1)可以简化为1。
insert into t2 (c, d)
select a, 1 as b1 from t1
on duplicate key update
d = values(b1);VALUES子句“错误”,改为
insert into t2 (c, d)
select a, 1 from t1
on duplicate key update
d = values(d);或者,在MySQL 8.0中:
insert into t2 (c, d)
select a, 1 from t1
on duplicate key update
d = NEW.d如果您有一个更复杂的例子,您可能需要向我们展示真正的问题所在。
发布于 2022-11-20 17:04:00
插入到t2 (c,d),选择a,(选择1)作为b1从t1上对重复的键更新d= t.b1;
这个查询中有两个表:t2 (in UPDATE子句)和t1 (in FROM子句)。您指的是t.b1 --但是查询文本中没有t表别名。
正确的变体:
insert into t2 (c, d)
select a, (select 1) as b1 from t1
on duplicate key update
-- d = t1.b1; -- this should be used when the column is used
d = 1; -- literal is used as a source value, so it may be simply repeatedinsert into t2 (c, d)
select a, (select 1) as b1 from t1 AS t
on duplicate key update
-- d = t.b1;
d = 1;插入到t2 (c,d),选择a,(选择1)作为b1从t1上对重复的键更新d=值(B1);
VALUES()的描述显式地告诉我们:
在
INSERT ... ON DUPLICATE KEY UPDATE语句中,可以使用UPDATE子句中的VALUES(col_name)函数引用语句的INSERT部分的列值。换句话说,VALUES(col_name)在UPDATE子句中是指在没有发生重复键冲突的情况下插入的col_name值。
也就是说,函数的参数是应该插入值的列的名称。查询是insert into t2 -所以只有表t2的列可以是VALUES()的参数。您使用values(b1) -但是t2中没有b1列。
正确的变体:
insert into t2 (c, d)
select a, (select 1) as b1 from t1
on duplicate key update
d = values(d);https://dba.stackexchange.com/questions/319940
复制相似问题