Re: Updating table with data from another table in the same database

Markus, > I have the daunting task of porting an Access database to > MySQL. It's unbeliveably poorly designed, and columns > id, name, issn, year, info, volume, numbers and remarks. > I made another table, names, that's got columns id and name. > > I used > > INSERT INTO names (name) SELECT DISTINCT mag_table.name FROM mag_table > > to get, as one would expect, the distinct magazine names into the names- > table. Now, what I'd want to do, is to replace the names in mag_table > with the id-numbers from the names table, but I can't figure out how > to do it. I tried > > UPDATE mag_table SET name=names.id WHERE name=names.name > > but MySQL tells me the following : > > ERROR 1109: Unknown table 'names' in where clause > > I haven't go perror to work at all, it just says Unknown error. > > Has anyone ever done anything like this, and, if so, how? > I was thinking about a PHP-script, but if someone has got > a query that would take care of this I wouldn't have to get into > that. =A daunting task indeed, however it's a move for the better, so more power to your right arm/kung-fu grip... However let's not jump to quickly to PHP, and keep it all within MySQL if we can... =So you started with one table: mag_table which contains the columns id, name, issn, year, info, volume, numbers and remarks. =Then you created a table called names (which was too nebulous for me, so I created table MagNames with columns MagId (auto_increment) and MagName). At which point we arrive at your question. =UPDATE only works on a single table - but you are right on the other criteria: that the row already exists. So a good first try, but sorry that one ain't going to get off the ground, Wilbur! =I always think that it would be logical to use a REPLACE here, but no REPLACE doesn't like a table REPLACING data within itself, unless the whole row is replaced - and even then it cannot refer to (copy values from) itself. So again, close but no coconut! =So we are pushed into the waiting arms of the INSERT command. Which requires an existing table and will cheerfully add new records. INSERT-ing data from another table/tables requires the INSERT...SELECT option (see manual 6.4.3 INSERT Syntax) =Accordingly I recommend CREATE-ing a new table, eg the prosaically named new_mag_table with the columns Id, MagId, ISSN, Year, Info, Volume, Numbers, Remarks - in other words, exactly the same as mag_table but with MagId instead of the (mag)name. Then work out how to join the two 'source' tables to create each record in turn (write the SELECT clause). Then construct the INSERT clause, slap the SELECT on its end, and the retrieved data will be inserted into the new_mag_table, eg: INSERT INTO new_mag_table ( Id, MagId, ISSN, Year, Info, Volume, Numbers, Remarks ) SELECT Id, MagNames.MagId, ISSN, Year, Info, Volume, Numbers, Remarks FROM MagNames, Mag_Table WHERE MagName = Name =once the new table is checked, if desired you can delete the original and rename the new... =NB if the (new_)mag_table Id field is auto_increment, do NOT set this in the new table (just in case there are discontinuities in the sequence (eg caused by DELETE-ions)). After checking and making the decision to adopt the new table, then modify the definition of the table/Id column, and the very next time you INSERT a record it will take on the next highest Id! =Ok? =dn