can these queries be combined into one?

On 2012-03-25, hamann.w@t-online.de <hamann.w@t-online.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

basically what you do is do a join and put some expressions in the
select list. I used coalesce here as it was a better fit, but case
can also be used...

perhaps this: (needs procucts to have a primary key whih will need to
be substituted in)

select distinct on ( p.PRIMARYY_KEY )
coalesce( t.ordercode|512, p.orderecode ) as ordercode ,
p.descr, p.codes
from products as p
left outer join othertable as t
on p.ordercode = t.ordercode and ....
where ... ;

--
⚂⚃ 100% natural

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

On Sun, 2012-03-25 at 08:41 +0000, hamann.w@t-online.de wrote:

Guillaume Lelarge <guillaume@lelarge.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

On Mar 25, 2012, at 1:50, Guillaume Lelarge <guillaume@lelarge.info> wrote:

On Sun, 2012-03-25 at 08:41 +0000, hamann.w@t-online.de wrote:

Guillaume Lelarge <guillaume@lelarge.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.

Hi,

On 25/03/12 08:16, hamann.w@t-online.de wrote:

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?

You just want to map some of the values in the codes field to different
values? If so, case/when should help, something like:

select ordercode, descr, case when ordercode = t.ordercode and .... then
codes | 512 else codes end as "codes" from products where ...

Note this is a read-only operation - some of the other answers seemed to
be updating the base tables, although maybe that's what you wanted?

cheers,

Tom

Solution heavily depends on the relation of products and othertable.

If ordercode at most has a single match with ordercode from othertable,
then a simple left join and a coalesce() or CASE in the select list will do
( SELECT p.ordercode, p.descr, CASE WHEN t.codes is NULL THEN
p.ordercode ELSE t.ordercode | 512
from products p left join othertable t on p.ordercode = t.ordercode
WHERE........ ;)

IF othertable has several matching rows you might need to applay an
aggregator adding in all the bits for codes....

However, your update statement seems to imply something along the first
case......

Regards
Rainer