On Mon, 1999-10-04 12:53:28 +0200, Ben-Nes Michael wrote:
> Martin Ramsch wrote:
[...]
> > For the time being, one of the solutions working around this
> > limitation is:
> >
> > - First get the primary key of the column to update:
[...]
> > - Then do the actual update:
[...]
> > 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');
[...]
> This method is relative easy when you have few code output.
> But what can I do if the SELECT output is not a few lines but hundreds ?
Just do the same. It works for thousand of rows, too.
The other method I know about uses REPLACE INTO and a temporary table:
CREATE TEMPORARY TABLE tmp type=HEAP
SELECT ...all_fields_of_product_with_Weight_replaced_by_0.5...
FROM products, rank
WHERE products.code= rank.code AND category='bell';
REPLACE INTO products SELECT * FROM tmp;
DROP TABLE tmp;
For versions older than V3.23.x you have to create the temporary table
first in a seperate step and fill it with INSERT INTO ... SELECT.
Regards,
Martin
--
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826 E4EC 8058 7B31 3AD7
