I would like to add a column to a table where the column to be added is in
another table. The column entries are to be matched using a particular
column from each table.

I have one table detailing phone calls, indexed by CallID. I have
created another table with the same index and a column CallNumber which I
want to add to the phone call table.

I have looked at documentation and in the book I have available without
success. Can anyone suggest appropriate syntax for this?

David Scot

__________________________________________________ _______________
David Scott Department of Statistics, Tamaki Campus
The University of Auckland, PB 92019
Auckland 1142, NEW ZEALAND
Phone: +64 9 373 7599 ext 86830 Fax: +64 9 373 7000
Email: d.scott@auckland.ac.nz

Graduate Officer, Department of Statistics
Director of Consulting, Department of Statistics

Hi David,

David Scott wrote:
>
> I would like to add a column to a table where the column to be added is
> in another table. The column entries are to be matched using a
> particular column from each table.
>
> I have one table detailing phone calls, indexed by CallID. I have
> created another table with the same index and a column CallNumber which
> I want to add to the phone call table.
>
> I have looked at documentation and in the book I have available without
> success. Can anyone suggest appropriate syntax for this?

Two queries come to mind. The first is the "official" SQL searched-update syntax:

update PhoneCalls set CallNumber = (
select CallNumber from OtherTable
where PhoneCalls.CallID=OtherTable.CallID);

The second is a multi-table update, which every RDBMS does differently:

update PhoneCalls as p
inner join OtherTable as o using(CallID)
set p.CallNumber=o.CallNumber;

Cheers
Baron