Thread: can these queries be combined into one?
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? Regards Wolfgang Hamann
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 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 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 onthe 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
On 25.03.2012 09:16, 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?
>
> Regards
> Wolfgang Hamann
>
>