Re: Update ordered

På mandag 27. januar 2014 kl. 16:23:56, skrev Adrian Klaver <
adrian(dot)klaver(at)gmail(dot)com <mailto:adrian(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com <mailto:adrian(dot)klaver(at)gmail(dot)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(at)officenet(dot)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