From: | <cnliou(at)eurosport(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | SubQuery |
Date: | 2001-10-04 01:06:04 |
Message-ID: | 200110040106.04c2@lh00.opsion.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | cnliou | 2001-10-04 01:18:53 | EXISTS Keyword |
Previous Message | Lee Harr | 2001-10-04 00:23:04 | Re: select 5/2??? |