Mahmoud Badreddine <[EMAIL PROTECTED]> wrote on 03/17/2005 03:16:47 PM:
> Hello, > I imported tow tables from an Access database to MySql. > Call them table1 and table2. > > Both tables came with unique identifiers (ID1 for table1 and ID2 for > table2)which were generated automatically as a regular integer > index(0,1,2...N-1) for an N set of data. I would like to get rid this > type of unique identifier. > > I created a primary key in table1 called "mainID". I then created a > new column in table2 also called mainID which will contain the > information of the new primary key which I created. > > I then issued the following mySql command: > > update table2 set table2.mainID=table1.mainID where table2.ID1=table1.ID1; > > I get the following error > ERROR 1054 (42S22): Unknown column 'ID1' in 'where clause' > > Somehow it doesn't recognize any columns from table1. > > > Your help is greatly apreciated... > > Further explanation for my query: > My reasoning for the condition is : for each row in table2 with a > certain value for ID1, find the matching value in table1for ID1. Then > take the corresponding mainID value on that row and stick it in that > row in table2. > I hope that was enlightening rather than more confusing. > > > > > > -- > -Mahmoud Badreddine You didn't tell your UPDATE statement anything about table1 as a source of data but you tried to use it in your WHERE clause. This will do what you wanted: update table2 INNER JOIN table1 on table2.ID1=table1.ID1 set table2.mainID=table1.mainID; For more information on forming UPDATE statements: http://dev.mysql.com/doc/mysql/en/update.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
