On Mar 25, 2012, at 1:50, Guillaume Lelarge <guillaume@xxxxxxxxxxxx> wrote: > On Sun, 2012-03-25 at 08:41 +0000, hamann.w@xxxxxxxxxxx wrote: >> Guillaume Lelarge <guillaume@xxxxxxxxxxxx> worte: >>>>> >>>>> Hi, >>>>> >>>>> I am currently doing something like >>>>> >>>>> select ordercode, descr, codes into temp table x from products where ... >>>>> Here codes is a bit-mapped field >>>>> update x set codes = codes | 512 from othertable t where ordercode = t.ordercode and .... >>>>> select * from x >>>>> >>>>> Is there a way to avoid that temp table? >>>>> >>>> >>>> Difficult to be really precise, but you may do this with: >>>> >>>> UPDATE products >>>> SET codes = codes | 512 >>>> FROM othertable t >>>> WHERE >>>> <your first-SELECT WHERE clause here> >>>> AND ordercode = t.ordercode AND ... >>>> RETURNING >>>> ordercode, descr, codes; >>>> >>>> You need at least 8.2 to use the RETURNING clause. >>>> >> >> Hi Guillaume, >> >> thanks a lot, but it is not really what I need: I want the whole lot, not just the rows that were part >> of the update >> > > Oops, you're right. Never answer before having some coffee :) > > I guess you don't have much choice then. You'll need that temp table. > > Not seeing the whole original query but you generally omit the where clause and move the condition to a case statement on the update. For records not meeting your criteria you simply update the column with the existing value. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
