From: Thimble Smith
Date: March 31 1999 10:50pm
Subject: Re: How to UPDATE from field in other table
List-Archive: http://lists.mysql.com/mysql/1253
Message-Id: <19990331155017.X11535@desert.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

You can't do a join in an update (or delete) statement.  So you
have to select the rows you want to update in one query, then
join the selected IDs into a string separated by commas, then
update WHERE iparty_sku IN (<string of selected IDs>).

Tim


On Wed, Mar 31, 1999 at 05:38:17PM -0500, Eric Berg wrote:
> 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
> 
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread1252@stripped
> 
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
>