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

Can't make it work, Eric.  Here's my actual code:

mysql> update products 
left join products on products.IPARTY_SKU=onhand.sku
set products.STOCK=onhand.qty
where products.IPARTY_SKU=onhand.sku;

ERROR 1064: parse error near 'left join products on products.IPARTY_SKU=onhand.sku set products.STOCK=onhand.q' at line 2

I'm missing something here.  What is it?

-Eric.


Eric Savage: [Wednesday 31-March]:

> Sorry, I meant tableA.primarykey=tableB.foreignkey
> 
> Desired row is the row that you want to update.  You can leave it out, in
> which case it will update all rows.
> 
> Eric Savage
> esavage@stripped
> 
> > 
> > Eric,
> > 
> > I'm afraid I don't follow you here.  First of all, why would my tableA
> > primary and foreign keys be equal -- they're not related.  Second of all,
> > where do you get the value of "desiredrow".
> > 
> > Can you clarify.
> > 
> > -Eric.
> > 
> > Eric Savage: [Wednesday 31-March]:
> > 
> > > Use a JOIN.
> > > 
> > > UPDATE tableA LEFT JOIN tableB ON tableA.primarykey=tableA.foreignkey
> > > SET tableA.somefield=tableB.otherfield
> > > WHERE tableA.primarykey=desiredrow;
> > > 
> > > Eric Savage
> > > esavage@stripped
> > > > 
> > > > 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.
> > -- 
> > Eric Berg
> > Chief Technical Officer, iParty Corp.               eberg@stripped
> > Vice President, New York Linux Users Group           eberg@stripped
> > work: 212-331-1229                          http://doobie.iparty.com
> > home: 212-721-2872
> > 
> 

-- 
Eric Berg
Chief Technical Officer, iParty Corp.               eberg@stripped
Vice President, New York Linux Users Group           eberg@stripped
work: 212-331-1229                          http://doobie.iparty.com
home: 212-721-2872