agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreak@officenet.no>
To: pgsql-sql@postgresql.org
Subject: Re: Update ordered
Date: Mon, 27 Jan 2014 16:37:28 +0100 (CET)
Message-ID: <OfficeNetEmail.29.db58afa68192a99d.143d456e56b@prod2> (raw)
In-Reply-To: <52E67A0C.5020605@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
På mandag 27. januar 2014 kl. 16:23:56, skrev Adrian Klaver <
adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.com>>: On 01/27/2014 07:01
AM, Andreas Joseph Krogh wrote:
> På mandag 27. januar 2014 kl. 15:56:12, skrev Adrian Klaver
> <adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.com>>:
>
> On 01/27/2014 06:36 AM, Andreas Joseph Krogh wrote:
> > Hi all.
> > I want an UPDATE query to update my project's project_number in
> > chronological order (according to the project's "created"-column) and
> > tried this:
> > with upd as(
> > select id from project order by created asc
> > ) update project p set project_number = get_next_project_number()
> from
> > upd where upd.id = p.id;
> > However, the olders project doesn't get the smalles project_number.
> > Any idea how to achive this?
>
> That would seem to depend on what get_next_project_number() does, the
> contents of which are unknown.
>
> get_next_project_number() gets the next project-number based on some
> custom logic.
> What would be the best way to update all project's project-number having
> the oldes project get the first number returned by
> get_next_project_number() etc.?
How are you sure it is not, have you tried something like below to test?:
with upd as(
select id from project order by created asc
) select p.id, p.create from project_number where upd.id = p.id; Yes, that
returns ordered result, but the update CTE doens't update with the oldest
project getting the first sequenc-nr. Using a DO statement, iterating over
all projects ordered by "created" then updating each project matching the
current iteration works, but I'd like to be able to do it in one statement as
I'm sure it's possible... --
Andreas Joseph Krogh <andreak@officenet.no> mob: +47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc
view thread (7+ messages) latest in thread
Message-ID: <OfficeNetEmail.29.db58afa68192a99d.143d456e56b@prod2>
Permalink: ../OfficeNetEmail.29.db58afa68192a99d.143d456e56b@prod2/
Also on: postgresql.org/message-id/OfficeNetEmail.29.db58afa68192a99d.143d456e56b@prod2
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: andreak@officenet.no
Subject: Re: Update ordered
In-Reply-To: <OfficeNetEmail.29.db58afa68192a99d.143d456e56b@prod2>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox