> 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
=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
=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 (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]