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.
>
