TB wrote:TB,> How do I add a new column with data to an existing table, ensuring
> correct record matching, using SQL?
> 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.
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 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,
Remove the "x" from my email address
JDS Computer Training Corp.