From: Baron Schwartz
Date: June 9 2007 3:10pm
Subject: Re: Add column from another table
List-Archive: http://lists.mysql.com/mysql/207333
Message-Id: <466AC2D0.9000205@xaprb.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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