pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSubQuery
2+ messages / 2 participants
[nested] [flat]
* SubQuery
@ 2001-10-04 01:06 cnliou@eurosport.com
0 siblings, 1 reply; 2+ messages in thread
From: cnliou@eurosport.com @ 2001-10-04 01:06 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SubQuery
@ 2001-10-04 01:24 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: cnliou@eurosport.com
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2001-10-04 01:24 UTC (permalink / raw)
To: cnliou@eurosport.com; +Cc: pgsql-sql
On Thu, 4 Oct 2001 cnliou@eurosport.com wrote:
> What mistake have I made?
>
> 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
I think you may need to disambiguate the s2.id=id
in the subquery to s2.id=mytable.id since both
have ids and it's probably assuming that means
s2.id=s2.id.
After changing that I get an UPDATE 1.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-10-04 01:24 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-04 01:06 SubQuery cnliou@eurosport.com
2001-10-04 01:24 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox