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 wrote:
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 (AT) attglobal (DOT) net
==================

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:

It worked perfectly.

Thanks a lot!

TB


Peter H. Coffin wrote:

Please permit me to ask another question, unrelated to this issue:

I have a MySQL table with the following fields

ID (int 11) (PK)
Company (varchar 50)
Contactdate (date)
Formdate (date)
Signdate (date)

I would like make a select query that order the records by the most
recent date (i.e. descending) of any of the three date fields and then
by company name. Some date fields may be empty (null) A record must not
be listed more once.

For example, if I have the following data:

ID, Company, Contactdate, Formdate, Signdate
1, acme1, 2006-01-01, 2006-01-10, Null
2, acme2, 2006-01-01, 2006-01-11, 2006-01-12
3, acme3, 2006-01-08, 2006-01-09, 2006-01-09

The result should be

2, acme2
1, acme1
3, acme3

In other words, the records should by ordered by the the 'latest date
field activity' of each record.

If it is complicated to use null values I can also set the default
value of the date fields to something like '1900-01-01'.

Thanks again,

TB


TB wrote:

TB wrote:
Read about the GREATEST() function here:
http://dev.mysql.com/doc/refman/5.0/...operators.html

For example:

SELECT *
FROM tablename
ORDER BY
GREATEST(
COALESCE(contactdate, '1900-01-01'),
COALESCE(formdate, '1900-01-01'),
COALESCE(signdate, '1900-01-01')
) DESC,
company ASC;

I wouldn't recommend storing any value to represent the absence of a
value. Keep the NULLs in your database, even if you have to use
COALESCE() to give them default values for purposes of the GREATEST()
function. It might not always be good to have 1900-01-01 in those columns.

Regards,
Bill K.

Excellent, that was exactly the function I was looking for.

Greatest() is not ANSI SQL, is it?

TB

Bill Karwin wrote:

TB wrote:
Right -- ANSI SQL includes surprisingly few functions. Most RDBMS
vendors implement a collection of functions as extensions to the SQL
language.

Regards,
Bill K.