> 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 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 WHERE
> 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 

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


PHP Database Mailing List (
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