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:

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! 

> =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!-)


Markus Lervik
Linux-administrator with a kungfoo grip
Vaasa City Library - Regional Library
+358-6-325 3589 / +358-40-832 6709

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to