[SQL] UPDATE FROM portability
Updating some rows in tab1 with corresponding values from tab2, pgsql style: UPDATE tab1 SET value=T2.VALUE FROM tab2 T2 WHERE T2.restr=1 AND tab1.key=T2.key<< The same for MSSQL: UPDATE tab1 SET value=T2.VALUE FROM tab1 T1 JOIN tab2 T2 ON T1.key=T2.Key << WHERE T2.restr=1 I'm looking for a portable query style, without using a subquery in the SET clause (which could make things quite slow) Any hints? Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Seeking help with a query....
> > Hi folks, seeking help with a query that I thought was simple, but > apparantly isn't, at least for someone with my knowledge level. > > Given a table : > > create table atable ( > code1 char, > code2 char, > costint > ); > > And the rows > > code1code2cost > - > ab2 > de4 > ba6 > fg1 > > I need a ( preferably single ) query that will sum the costs for any > matching pairs of codes regardless of order. That is, row 1 and row 3 > concern the same pair of unordered codes (a,b), and the result should show > that the (a,b) pair had a summed cost of 8. I am not able to change any of > the environment or preconditions other than the query itself. I have tried > so many approaches that they aren't even worth listing. Any suggestions > would be very much appreciated. > My approach is inspired by PostgreSQL 7.3 Set Returning Functions by Stephan Szabo ( http://techdocs.postgresql.org/guides/SetReturningFunctions ) Use a set returning function to get ordered pairs and do a group by then as create type aholder as ( code1 char, code2 char, costint ); create or replace function aordered() returns setof aholder as ' declare myrow aholder%ROWTYPE; codex char; begin for myrow in select code1,code2,cost from atable loop if myrow.code1 > myrow.code2 then codex := myrow.code1; myrow.code1 := myrow.code2; myrow.code2 := codex; end if; return next myrow; end loop; return; end ' language 'plpgsql'; select code1,code2,sum(cost) from aordered() group by code1,code2 ; code1 | code2 | sum ---+---+- a | b | 8 d | e | 4 f | g | 1 (3 rows) In addition if you want only matching pairs (eliminate single pairs) try create or replace function amatched() returns setof aholder as ' declare myrow aholder%ROWTYPE; codex char; begin for myrow in select t1.code1,t1.code2,t1.cost from atable t1,atable t2 where t1.code1=t2.code2 and t1.code2=t2.code1 loop if myrow.code1 > myrow.code2 then codex := myrow.code1; myrow.code1 := myrow.code2; myrow.code2 := codex; end if; return next myrow; end loop; return; end ' language 'plpgsql'; select code1,code2,sum(cost) from amatched() group by code1,code2 ; code1 | code2 | sum ---+---+- a | b | 8 (1 row) Hope this helps. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Complex outer joins?
Sorry if I was not that clear...but in fact the (+) is the join operator in Oracle. The statement in question is about making 3 left outer joins on 3 diferent tables. G, L and C are in fact 3 diferent tables. Carla -Ursprüngliche Nachricht- Von: Peter Childs [mailto:[EMAIL PROTECTED] Gesendet: Monday, March 24, 2003 11:44 AM Cc: '[EMAIL PROTECTED]' Betreff: Re: [SQL] Complex outer joins? On Mon, 24 Mar 2003, Correia, Carla wrote: > > Hi, > > I've got PsotgreSQL 7.3. > > My problem is joins. I've seen the syntax on joins and have sucessefully > used SQLs with joins, but I've got some other big SQL statements using many > and complex > joins. > Simplified example: > > select G.SELID, G.TEXT, > L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT, > C.ID as KRITERIENFELDID, C.SELFLD > from G, L, C > where > and G.SELID = L.SELID (+) > and L.SELID = C.SELID (+) > and L.SELLEVEL = C.SELLEVEL (+) > > How can i write this in Postgres? As the problem is that I use one same > table for varios joins. This is a problem in Postgres. Any ideas? > > When I use the same table in say 3 or 4 joins in one SQL, is the only > solution unions?? Adn if so, the performance will certainly suffer? > I don't quite understand you question. but I presume that G, L, and C are in fact the same table. I'm not sure what you mean by the (+) more clarity is really required. If however G, L, and C are the same table this query can be rewritten as select G.SELID, G.TEXT, L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT, C.ID as KRITERIENFELDID, C.SELFLD from table as G, table as L, table as C where and G.SELID = L.SELID and L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL; Where table is the name of the table I hope that helps Peter Childs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Complex outer joins?
Password: d5B9Av Hi, I've got PsotgreSQL 7.3. My problem is joins. I've seen the syntax on joins and have sucessefully used SQLs with joins, but I've got some other big SQL statements using many and complex joins. Simplified example: select G.SELID, G.TEXT, L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT, C.ID as KRITERIENFELDID, C.SELFLD from G, L, C where and G.SELID = L.SELID (+) and L.SELID = C.SELID (+) and L.SELLEVEL = C.SELLEVEL (+) How can i write this in Postgres? As the problem is that I use one same table for varios joins. This is a problem in Postgres. Any ideas? When I use the same table in say 3 or 4 joins in one SQL, is the only solution unions?? Adn if so, the performance will certainly suffer? thanks! Carla This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] query 2 database
> > Can anybody help me, can't i make "query > 1 table in different database > (but not schema)" in postgre 7.3? > see $PGSQLD/contrib/dblink/ Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Complex outer joins?
On Mon, 24 Mar 2003, Correia, Carla wrote: > > Hi, > > I've got PsotgreSQL 7.3. > > My problem is joins. I've seen the syntax on joins and have sucessefully > used SQLs with joins, but I've got some other big SQL statements using many > and complex > joins. > Simplified example: > > select G.SELID, G.TEXT, > L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT, > C.ID as KRITERIENFELDID, C.SELFLD > from G, L, C > where > and G.SELID = L.SELID (+) > and L.SELID = C.SELID (+) > and L.SELLEVEL = C.SELLEVEL (+) > > How can i write this in Postgres? As the problem is that I use one same > table for varios joins. This is a problem in Postgres. Any ideas? > > When I use the same table in say 3 or 4 joins in one SQL, is the only > solution unions?? Adn if so, the performance will certainly suffer? > I don't quite understand you question. but I presume that G, L, and C are in fact the same table. I'm not sure what you mean by the (+) more clarity is really required. If however G, L, and C are the same table this query can be rewritten as select G.SELID, G.TEXT, L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT, C.ID as KRITERIENFELDID, C.SELFLD from table as G, table as L, table as C where and G.SELID = L.SELID and L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL; Where table is the name of the table I hope that helps Peter Childs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Complex outer joins?
Hi, I've got PsotgreSQL 7.3. My problem is joins. I've seen the syntax on joins and have sucessefully used SQLs with joins, but I've got some other big SQL statements using many and complex joins. Simplified example: select G.SELID, G.TEXT, L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT, C.ID as KRITERIENFELDID, C.SELFLD from G, L, C where and G.SELID = L.SELID (+) and L.SELID = C.SELID (+) and L.SELLEVEL = C.SELLEVEL (+) How can i write this in Postgres? As the problem is that I use one same table for varios joins. This is a problem in Postgres. Any ideas? When I use the same table in say 3 or 4 joins in one SQL, is the only solution unions?? Adn if so, the performance will certainly suffer? thanks! Carla This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Seeking help with a query....
* Dan Winslow <[EMAIL PROTECTED]> [21.03.2003 21:58]: > Hi folks, seeking help with a query that I thought was simple, but > apparantly isn't, at least for someone with my knowledge level. > > Given a table : > > create table atable ( > code1 char, > code2 char, > costint > ); > > And the rows > > code1code2cost > - > ab2 > de4 > ba6 > fg1 > > I need a ( preferably single ) query that will sum the costs for any > matching pairs of codes regardless of order. That is, row 1 and row 3 > concern the same pair of unordered codes (a,b), and the result should show > that the (a,b) pair had a summed cost of 8. I am not able to change any of > the environment or preconditions other than the query itself. I have tried > so many approaches that they aren't even worth listing. Any suggestions > would be very much appreciated. First thing, that came to my mind: Give each code (if they are not numeric) a number. For this example, that could be ASCII code of chars. Create view on that table: CREATE VIEW aview AS SELECT at.*, code(at.code1) + code(at.code2) AS dbl_code FROM atable at; dbl_code field will have equal values for all groups of codes with same codes involved: a and b, b and a. About function code() I used to create the View: it's just an assumption, you should write one yourself (on C for faster perfomance). Or simply use: code1 + code if your codes are of numeric type and are foreign keys to some other table's primary key. Please, give some feedback on usability of this solution. -- Victor Yegorov pgp0.pgp Description: PGP signature
[SQL] Which Approach Performs Better?
Hi! I have a tree table: CREATE TABLE tree ( CONSTRAINT fktree FOREIGN KEY (parent) REFERENCES tree (dept), dept int primary key, --department parent int ); insert into tree values(1,1); insert into tree values(2,1); insert into tree values(3,2); and a history table: CREATE TABLE history ( CONSTRAINT fkhistory FOREIGN KEY (dept) REFERENCES tree (dept), dept int primary key, --department amount int ); insert into history values(1,100); insert into history values(2,200); insert into history values(3,300); My purpose is to retrieve the amount detail of department "1" and all departments under it. I have come out with 2 approaches: APPROACH A: --Returns TRUE if department $2 reports to department $1. CREATE FUNCTION IsChild(TEXT,TEXT) RETURNS BOOLEAN AS ' DECLARE p ALIAS FOR $1; --parent c ALIAS FOR $2; --child vparent INT; BEGIN IF c = p THEN RETURN TRUE; END IF; SELECT parent INTO vparent FROM tree WHERE dept=c; IF NOT FOUND THEN RETURN FALSE; ELSE RETURN IsChild(p,vparent); END IF; END' LANGUAGE 'plpgsql' STABLE; SELECT amount FROM history WHERE IsChild(1,dept); - APPROACH B: (Assuming the number of layers of this tree is predicatable. Let's take 3 layers as an example.) SELECT amount FROM history WHERE dept=1 OR dept IN (SELECT dept FROM tree WHERE parent=1) OR dept IN (SELECT dept FROM tree WHERE parent IN (SELECT dept FROM tree WHERE parent=1)); Both queries return amount 100 200 300 (3 rows) APPROACH A is obviously easier to implement. My question is which approach gets better performance when the number of rows in history and the layers in tree grows? I don't intend to apply "Joy's worm" tree algorism as it is too complicate to me to understand. Thank you in advance for any input! Regards, CN -- http://www.fastmail.fm - A no graphics, no pop-ups email service ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
