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

On Wednesday 09 January 2002 19:49, you wrote: > =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... I was hoping we could ;-) > =So you started with one table: mag_table which contains the columns id, > name, issn, year, info, volume, numbers and remarks. Right. That was the one I got dumped out of Access97 and then modified it a bit with ALTER TABLE. I probably should have included the output from DESCRIBE mag_table and DESCRIBE names, but I forgot.. : ) > =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! That's stupid and quite illogic. One would think that UPDATE would update a column if one tells it to update a column and if one tells it to, it should use data from another table. Why can't it work the way -I- want it to?-) > =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! Yes, I figured that one out while reading the manual. Bummer. That's also a bit stupid. Doesn't quite make any sense. > =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) Yes, I know, I used it to populate the names (or, if you prefer, mag_names) table. INSERT INTO names (name) SELECT DISTINCT blablabla. > =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: [snip] Yes, I remember that I had an idea like that a while back, but then I forgot it :-) One tends to forget things after working for 11-12 hours/day three days in a row... : ) I did just that now, and it works like a charm! [/snip] > =Ok? Very much so! Thanks a million! Now we're (or rather, I'm) a bit wiser again, and I increased the grasp of my kung-foo grip just a bit more!-) Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library markus.lervik@vaasa.fi +358-6-325 3589 / +358-40-832 6709