Professional Web Applications Themes

adding a column with data - MySQL

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

  1. #1

    Default adding a column with data

    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 Guest

  2. #2

    Default Re: adding a column with data

    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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: adding a column with data

    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.
    > [email]jstucklexattglobal.net[/email]
    > ==================
    TB Guest

  4. #4

    Default Re: adding a column with data

    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 Guest

  5. #5

    Default Re: adding a column with data

    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 Guest

  6. #6

    Default Re: adding a column with data

    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:
    [url]http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html[/url]

    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.
    Bill Karwin Guest

  7. #7

    Default Re: adding a column with data

    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:
    > [url]http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html[/url]
    >
    > 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 Guest

  8. #8

    Default Re: adding a column with data

    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.
    Bill Karwin Guest

Similar Threads

  1. adding on a column...
    By nobuko_nagaoka@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 4
    Last Post: August 21st, 09:26 PM
  2. Getting column data without column names
    By Jared@Itron in forum Coldfusion Database Access
    Replies: 4
    Last Post: January 18th, 09:26 PM
  3. Adding a href link to a column in a data grid
    By Vasantha peddireddy in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: August 23rd, 07:19 PM
  4. Template Column data depending on form data
    By John Mackerras in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: November 15th, 01:05 PM
  5. DataGrid - Adding labels: and adding data to cells
    By bobobob webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: January 19th, 09:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139