List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 4 1999 11:52pm
Subject:Re: More Complex UPDATE
View as plain text  
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
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