Re: can these queries be combined into one?

On Mar 25, 2012, at 1:50, Guillaume Lelarge <guillaume(at)lelarge(dot)info> wrote:

> 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.
>
>

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.