SEO

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

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

On 21 Jul 2006 05:51:22 -0700, TB wrote:
> 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?

That makes it easy. You first alter the table to add the column to house
the data you want to add, then update that coloumn to contain the value
of the other table.

ALTER TABLE maintable
ADD cellphone VARCHAR(20) AFTER fax;

UPDATE maintable JOIN extratable ON maintable.id = extratable.id
SET maintable.cellphone = extratable.cellphone;

I haven't tested the above, but that should give you the idea.

--
81. If I am fighting with the hero atop a moving platform, have disarmed him,
and am about to finish him off and he glances behind me and drops flat, I
too will drop flat instead of quizzically turning around to find out what
he saw. --Peter Anspach's list of things to do as an Evil Overlord

It worked perfectly.

Thanks a lot!

TB


Peter H. Coffin wrote:
> On 21 Jul 2006 05:51:22 -0700, TB wrote:
> > 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?
>
> That makes it easy. You first alter the table to add the column to house
> the data you want to add, then update that coloumn to contain the value
> of the other table.
>
> ALTER TABLE maintable
> ADD cellphone VARCHAR(20) AFTER fax;
>
> UPDATE maintable JOIN extratable ON maintable.id = extratable.id
> SET maintable.cellphone = extratable.cellphone;
>
> I haven't tested the above, but that should give you the idea.
>
> --
> 81. If I am fighting with the hero atop a moving platform, have disarmed him,
> and am about to finish him off and he glances behind me and drops flat, I
> too will drop flat instead of quizzically turning around to find out what
> he saw. --Peter Anspach's list of things to do as an Evil Overlord

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:
> It worked perfectly.
>
> Thanks a lot!
>
> TB
>
>
> Peter H. Coffin wrote:
> > On 21 Jul 2006 05:51:22 -0700, TB wrote:
> > > 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?
> >
> > That makes it easy. You first alter the table to add the column to house
> > the data you want to add, then update that coloumn to contain the value
> > of the other table.
> >
> > ALTER TABLE maintable
> > ADD cellphone VARCHAR(20) AFTER fax;
> >
> > UPDATE maintable JOIN extratable ON maintable.id = extratable.id
> > SET maintable.cellphone = extratable.cellphone;
> >
> > I haven't tested the above, but that should give you the idea.
> >
> > --
> > 81. If I am fighting with the hero atop a moving platform, have disarmed him,
> > and am about to finish him off and he glances behind me and drops flat, I
> > too will drop flat instead of quizzically turning around to find out what
> > he saw. --Peter Anspach's list of things to do as an Evil Overlord

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

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;

> 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'.

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:
> > In other words, the records should by ordered by the the 'latest date
> > field activity' of each record.
>
> 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;
>
> > 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'.
>
> 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.

TB wrote:
> Greatest() is not ANSI SQL, is it?

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

Regards,
Bill K.