List:General Discussion« Previous MessageNext Message »
From:Eric Berg Date:March 31 1999 10:38pm
Subject:Re: How to UPDATE from field in other table
View as plain text  
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
Thread
How to UPDATE from field in other tableEric Berg31 Mar
  • Re: How to UPDATE from field in other tableChristian Mack31 Mar
    • Re: How to UPDATE from field in other tableEric Berg1 Apr
  • Re: How to UPDATE from field in other tableSasha Pachev1 Apr
    • Re: How to UPDATE from field in other tableEric Berg1 Apr
Re: How to UPDATE from field in other tableEric Berg1 Apr
  • Re: How to UPDATE from field in other tableThimble Smith1 Apr