Re: can these queries be combined into one?

On Sun, 2012-03-25 at 07:16 +0000, hamann(dot)w(at)t-online(dot)de wrote:
>
> 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.

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