Description:
When doing a mysql query like below:
"INSERT INTO table2 (field1,field2,field3,field4,field5,field6,field7) select table1.field1,table1.field2,table1.field3,table1,field4,table1,field5,table1,field6,table1.field7 from table1 group by table1.field1 ON DUPLICATE KEY UPDATE table2.field3 = table1.field3, table2.field4 = table1.field4, table2.field5 = table1.field5,table2.field6 = table1.field6,table2.field7 = table1.field7";
I have "table1" setup with an primary index of type "int" and "table2" has primary key of type varchar(45). I am expecting this command to insert field1-field7 from table1 into table2. I created "table2" from "table1" before using this statement so fields are matched perfectly. When there is a duplicate key or value on table2.field1 found then I want the program to update table2 fields with any changes made to "field3-field7" on table1. I get the error below when i run the statement.
mysql errors = Unknown column 'table1.field1' in 'field list'1054
I have triple checked both tables and the fields are named correctly in both, and in the mysql query
How to repeat:
"INSERT INTO table2 (field1,field2,field3,field4,field5,field6,field7) select table1.field1,table1.field2,table1.field3,table1,field4,table1,field5,table1,field6,table1.field7 from table1 group by table1.field1 ON DUPLICATE KEY UPDATE table2.field3 = table1.field3, table2.field4 = table1.field4, table2.field5 = table1.field5,table2.field6 = table1.field6,table2.field7 = table1.field7";
sorry for the number of fields I am using but wanted to give who ever is working on this an exact example of what I am doing.
Suggested fix:
I fixed this query by doing the following and I get the results that I want...
"INSERT INTO table2 (field1,field2,field3,field4,field5,field6,field7) select table1.field1,table1.field2,table1.field3,table1,field4,table1,field5,table1,field6,table1.field7 from table1 group by table1.field1 ON DUPLICATE KEY UPDATE table2.field3 = values(field3), table2.field4 = values(field4), table2.field5 = values(field5),table2.field6 = values(field6),table2.field7 = values(field7)";
NOTE: using values(table1.field3) will give the same error I started with
