Thanks for replying. The truth is I can actually add another column to
the table 'extratable' which contains the same unique values as are
being used in the primary key column in 'maintable'

I would therefore have:

maintable:
ID (PK) (int 11)
firstname (varchar 40)
lastname (varchar 40)
phone (varchar 20)
fax (varchar 20)

extratable

ID (int 11)
cellphone (varchar 20)

I can make ID the PK of extratable.

Does that change the situation?

TB

Jerry Stuckle wrote:
> TB wrote:
> > How do I add a new column with data to an existing table, ensuring
> > correct record matching, using SQL?
> >
> > Situation:
> >
> > I have a table named 'maintable' with 5 columns (ID, firstname,
> > lastname, phone and fax) and 1000 records. ID is the primary key. Now I
> > need to add (import) a new column (cellphone) already containing data
> > from a one-column table called 'extratable'.
> >
> > The order of the records in both tables are identical, in other words
> > the cellphone number in record 37 of the table 'extratable' corresponds
> > to record 37 the table 'maintable'.
> >
> > Or would it be better to create a new table, merging the two existing
> > tables. If so, how is that done?
> >
> > Database engine: MySQL
> >
> > Thanks in advance.
> >
> > TB
> >
>
> TB,
>
> SQL tables by definition are unordered. As rows are deleted and added,
> new rows may be inserted in the middle. for instance.
>
> If you do a SELECT * on the table, the rows will be as they appear in
> the table. But that's not "ordered".
>
> The only safe way to data to a new column is to update the rows based on
> the primary key (or other unique data). The problem here is - your cell
> phone table has only one column, so it doesn't have an ID associated
> with it.
>
> With this information I don't know of any way you could be sure the rows
> are in exactly the same order. The best would be to go back to wherever
> the cellphone data was extracted from and get the id or other unique
> information along with it.
>
> You can't import a single column; neither can you just merge two tables.
> I guess you could write a program in Perl, PHP, etc. which would get a
> row from the main table, a row from the extract table and update the one
> in the main table. But no way to do it with SQL itself. And it only
> works if the two tables are in exactly the same order with no additions,
> deletions, etc.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================