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
