pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: cnliou@eurosport.com
To: pgsql-sql@postgresql.org
Subject: SubQuery
Date: Thu, 4 Oct 2001 01:06:04 GMT
Message-ID: <200110040106.04c2@lh00.opsion.fr> (raw)

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








view thread (2+ messages)  latest in thread

Message-ID: <200110040106.04c2@lh00.opsion.fr>
Permalink:  ../200110040106.04c2@lh00.opsion.fr/
Also on:    postgresql.org/message-id/200110040106.04c2@lh00.opsion.fr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: cnliou@eurosport.com
  Subject: Re: SubQuery
  In-Reply-To: <200110040106.04c2@lh00.opsion.fr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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