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
