List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 28 1999 8:37am
Subject:Re: More Complex UPDATE
View as plain text  
On Mon, 1999-09-27 16:40:08 +0200, Ben-Nes Michael wrote:
> Im trying to:
> update products SET Weight="0.5"
> where (products.code = rank.code) and category= "bell";
> 
> then its says: Unknown table 'rank' in where clause

On Mon, 1999-09-27 13:06:45 -0400, Bob Kline wrote:
> You need a FROM ... clause.
> 
> UPDATE  products
> SET     Weight = '0.5'  /* is this really a character column? */
> FROM    products, 
>         rank
> WHERE   products.code = rank.code
> AND     category = 'bell';
> 
> Unfortunately, it doesn't look as if this syntax is supported
> by MySQL (based on a review of the manual and some experimenting).

For the time being, one of the solutions working around this
limitation is:

- First get the primary key of the column to update:
    SELECT ...fields_of_primary_key_of_products...
    FROM   products, rank
    WHERE  products.code = rank.code
      AND  category = 'bell';

- Then do the actual update:
    UPDATE products
    SET Weight = '0.5'
    WHERE ...fields_of_primary_key_of_products... = ...stored_value1...
       OR ...fields_of_primary_key_of_products... = ...stored_value2...;

If for example products.code _is_ the primary key of products, then
it might be:
    SELECT products.code FROM products, rank
    WHERE  products.code= rank.code AND category='bell';
    --> yields a list of codes, maybe 'a', 'd', 'e'.
    UPDATE products SET Weight='0.5' WHERE code IN ('a','d','e');

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
More Complex UPDATEBen-Nes Michael27 Sep
  • RE: More Complex UPDATEbkline27 Sep
    • Re: More Complex UPDATEBen-Nes Michael27 Sep
    • Re: More Complex UPDATEMartin Ramsch28 Sep
  • Re: More Complex UPDATEBen-Nes Michael4 Oct
    • Re: More Complex UPDATEMartin Ramsch5 Oct
    • Re: More Complex UPDATEMartin Ramsch5 Oct