[SQL] SubQuery

[cnliou]

Hi!

I am using MyTable to serve the "next number"
functionality like PGSQL embeded "sequence" offers.
The only difference of the two is MyTable has 2 more
fields - "ID" and "Starting Effective Date".

The UPDATE SQL fails when I am trying to update
record

 ID1 | 2001-08-01| 11

to

 ID1 | 2001-08-01| 12

What mistake have I made?

CN
=
database1=# select * from mytable;
 id  | effectivedate | nextnumber
-+---+
 ID1 | 2001-06-01| 10
 ID1 | 2001-07-01|  3
 ID1 | 2001-08-01| 11
 ID2 | 2001-09-01| 35
(4 rows)

database1=# SELECT EffectiveDate,NextNumber FROM
mytable s1
database1-# WHERE id='ID1' AND EffectiveDate=
database1-# (SELECT MAX(EffectiveDate) FROM mytable
s2
database1(# WHERE s1.id=s2.id AND
s2.EffectiveDate<=CURRENT_DATE);
 effectivedate | nextnumber
---+
 2001-08-01| 11
(1 row)

database1=# UPDATE mytable SET
NextNumber=NextNumber+1
database1-# WHERE id='ID1' AND EffectiveDate=
database1-# (SELECT MAX(s2.EffectiveDate) FROM
mytable s2
database1(# WHERE s2.id=id AND
s2.EffectiveDate<=CURRENT_DATE);
UPDATE 0
database1=# UPDATE mytable SET
NextNumber=NextNumber+1
database1-# WHERE id='ID2' AND EffectiveDate=
database1-# (SELECT MAX(s2.EffectiveDate) FROM
mytable s2
database1(# WHERE s2.id=id AND
s2.EffectiveDate<=CURRENT_DATE);
UPDATE 1
database1=# select * from mytable;
 id  | effectivedate | nextnumber
-+---+
 ID1 | 2001-06-01| 10
 ID1 | 2001-07-01|  3
 ID1 | 2001-08-01| 11
 ID2 | 2001-09-01| 36
(4 rows)


You too can have your own email address from Eurosport.
http://www.eurosport.com






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

[SQL] EXISTS Keyword

[cnliou]

Hello!

I vaguely remember that someone said somewhere that
EXISTS runs faster than IN:

SELECT * FROM table1 where field1 EXISTS (SELECT
field4 FROM table2)

However, all I got from version 7.1.3 is:

ERROR:  parser: parse error at or near "exists"

While below works:

SELECT * FROM table1 where field1 IN (SELECT field4
FROM table2)

Any key points I have missed?

CN


You too can have your own email address from Eurosport.
http://www.eurosport.com






---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] Group And Sort After Union

[cnliou]

Greetings!

I want to GROUP BY and ORDER BY on the result of UNION similar to the
following (wrong) SQL:

(SELECT c11 AS c1,SUM(c12) AS c2 FROM table1
UNION
SELECT c21 AS c1,SUM(c22) AS c2 FROM table2
)
GROUP BY c1
ORDER BY c2;

Please note that the following is NOT what I want because it generates
2 groups of data set:

SELECT c11 AS c1,SUM(c12) AS c2 FROM table1
GROUP BY c1
ORDER BY c2
UNION
SELECT c21 AS c1,SUM(c22) AS c2 FROM table2
GROUP BY c1
ORDER BY c2;

How do I do that? Thank you in advance!

CNLIOU

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org