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

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

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.

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

A view?
A materialized view?

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