From: Eric Berg
Date: March 31 1999 10:37pm
Subject: Re: How to UPDATE from field in other table
List-Archive: http://lists.mysql.com/mysql/1251
Message-Id: <14082.41916.856162.517678@doobie.spacelab.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Christian,

Replace will kill the rest of the row, only assigning values where you
specify (or to *).  Not what I want here.

I'm having no luck with the UPDATE one table from another approach, either.
I think I'm going to create a file of update statements based on the source
table.  Actually, I've done it already with a Perl script, but I wanted to do 
it within the SQL server.  Ah, well.

The one problem that I have is that I'd like to see the changes even for rows 
where the replacement/update value is the same as the current value. 

-Eric.

Christian Mack: [Wednesday 31-March]:

> Eric Berg wrote:
> > 
> > I'm trying to update a field in tableA to a value in a field in tableB based
> > on a common key.  I get "ERROR 1109: Unknown table 'tableB' in field list"
> > errors each time.
> > 
> > update tableA
> > set somefield=tableB.otherfield
> > where tableA.keyfield=tableB.keyfield;
> > 
> > How do I work around this?
> > 
> > -Eric.
> 
> Hi Eric
> 
> The above is illegal SQL, because UPDATE uses only one table.
> 
> Try the REPLACE ... SELECT ... syntax.
> 
> Tschau
> Christian
>