#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11

    Append columns from one table to another


    Hey all,

    Perhaps this is a common request : I have 2 tables:

    Table A
    -------
    ID Value
    1 a
    2 b
    3 c

    Table B
    -------
    ID AnotherValue
    1 x
    2 y

    I am hoping to append a column from Table B to Table A based on a simple sql join (e.g:

    Table A

    ID Value AnotherValue
    1 a x
    2 b y
    3 c (null)

    )

    I would rather stay away from the standard update statement since it takes far to long and I'd prefer not to use create table as I don't want to duplicate any data...is this possible to do ? (e.g: just insert the columns into this table ?) - or if it's possible the performance overhead just wouldn't make it worth it ?

    Regards
    Ben
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by amstel_za
    I am hoping to append a column from Table B to Table A based on a simple sql join (e.g:

    Table A

    ID Value AnotherValue
    1 a x
    2 b y
    3 c (null)

    )
    So you want to add a new column to table A?

    I would rather stay away from the standard update statement
    So you want to update data without using the UPDATE statement? Sounds like an impossible task.

    If you do want to add a new column to TableA, you will have to do an alter table add column followed by an UPDATE statement to put the correct values into the new (empty) column.

    The only other option you have, is to create a view that returns the joined data.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    Damn...guess it's the create table as solution - faster than the update but unnecessary duplication of some of the data...

    kinda hoped there would be a permanent join option for/to an existing table.

    Cheers for the quick response Shammat. Much appreciated
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by amstel_za
    kinda hoped there would be a permanent join option for/to an existing table.
    A view?
    A materialized view?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    Yep, looked at both, but because of the size of the table I'll probably look to try and create a series of smaller materialized views rather than a view. The data will need to be accessible very quickly so I need to stay away from running the query whenever a request comes in...so mview is going to offer the best approach I think (even if it comes with a storage cost).

    Thanx Shammat...appreciate the advice

IMN logo majestic logo threadwatch logo seochat tools logo