2

I am looking for something similar-

insert into table1(a, b, c)
select col1 as d, col2 as e, col3 as f from table2
on duplicate key update b = e, c = f;

NOTE - Here table1.a is unique Key.

I am getting error- Unknown column name 'e'.

Is there any solution to use 'On duplicate key' with 'Insert into Select' statement?

Thanks!

e4c5
  • 52,766
  • 11
  • 101
  • 134
Abhi
  • 1,963
  • 7
  • 27
  • 33

1 Answers1

2

Confusion caused by use of alias

insert into table1(a, b, c)
select col1 as d, col2 as e, col3 as f from table2
on duplicate key update b = table2.col2, c = table2.col3;
e4c5
  • 52,766
  • 11
  • 101
  • 134
  • Yes I have tried both... Its not working. And I want to update table 2 data into table1, so value(a) and value(b) will not work. – Abhi Feb 17 '17 at 13:05
  • Yes..I have tried that as well... Not working. its showing same error – Abhi Feb 17 '17 at 13:16
  • Nopes, this one works. I am now at a mysql console and I tried it before posting. – e4c5 Feb 17 '17 at 13:16
  • I mean similar error. Error Code: 1054. Unknown column 'table2.col2' in 'field list' – Abhi Feb 17 '17 at 13:20
  • Hey... actually it was not working because of group by in select query. :-) on duplicate key update b = t2.col2, c = t2.col3; -- this is working – Abhi Feb 17 '17 at 14:50