4

Possible Unanswered Duplicate Question: Insert select - using selected value in ON DUPLICATE KEY UPDATE

I am not able to use a subquery result column in the select clause as a value to update in a insert into ....(select... ) update on duplicate query.

Getting Error Code: 1054. Unknown column in 'field list' error.

Attached reduced and simple repro steps.

My original case involves 15 columns and too many complicated JSON_TABLEs. I have a work around that repeats the whole subquery again in the update clause, but that doubles the query execution time compared to omitting that column and updating.

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

2 Answers 2

1

(SELECT 1) can be simplified to just 1.

insert into t2 (c, d) 
        select a, 1 as b1 from t1
    on duplicate key update
    d = values(b1);

The VALUES clause is 'wrong', change to

insert into t2 (c, d) 
        select a, 1 from t1
    on duplicate key update
    d = values(d);

Or, in MySQL 8.0:

insert into t2 (c, d) 
        select a, 1 from t1
    on duplicate key update
    d = NEW.d

If you have a more complex example, you may need to back up to show us what the real problem is.

3
insert into t2 (c, d) 
select a, (select 1) as b1 from t1
on duplicate key update
d = t.b1;

There are 2 tables in this query: t2 (in UPDATE clause) and t1 (in FROM clause). You refer to t.b1 - but there is no t table alias in the query text.

Correct variants:

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 repeated
insert into t2 (c, d) 
select a, (select 1) as b1 from t1 AS t
on duplicate key update
-- d = t.b1;
d = 1;

insert into t2 (c, d) 
select a, (select 1) as b1 from t1
on duplicate key update
d = values(b1);

The description of the VALUES() explicitly tells:

In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

I.e. an argument of the function is a name of a column which the value should be inserted into. The query is insert into t2 - so only the column of the table t2 can be an argument of VALUES(). You use values(b1) - but there is no b1 column in t2.

Correct variant:

insert into t2 (c, d) 
select a, (select 1) as b1 from t1
on duplicate key update
d = values(d);
6
  • Are you sure that this works? Did you test it? insert into t2 (c, d) select a, (select 1) as b1 from t1 on duplicate key update d = t1.b1; Since t1 has no column b1, I'd expect it to fail. Commented Nov 20, 2022 at 17:10
  • This should work though (enclosing the whole select in a derived table): insert into t2 (c, d) select * from (select a, (select 1) as b1 from t1) as t on duplicate key update d = t.b1; Commented Nov 20, 2022 at 17:12
  • The d = values (d) suggestion looks good. Commented Nov 20, 2022 at 17:13
  • The enclosing one works. Thanks. ypercube. Post as answer and I will mark it.
    – Arun S R
    Commented Nov 20, 2022 at 18:55
  • I think @Akina just meant the correct variant of the question that will throw error that I am facing as per my question. And for that - My bad didnt read the doc well. it should be insert statement col name. not select statement
    – Arun S R
    Commented Nov 20, 2022 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.