Re: can these queries be combined into one?

On Sun, 2012-03-25 at 08:41 +0000, hamann(dot)w(at)t-online(dot)de wrote:
> Guillaume Lelarge <guillaume(at)lelarge(dot)info> 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.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com