Re: Update ordered

On 01/27/2014 07:37 AM, Andreas Joseph Krogh wrote:

> > 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...

Well two things;

1) Knowing what is in get_next_project_number() would be helpful?

2) Absent the above I do not see how:

update project p set project_number = get_next_project_number() from upd
where upd.id = p.id;

will actually work. No argument is being passed to
get_next_project_number() so I am not sure how it picks up what
id/created or other reference it is actually working with.

This is borne out by your success using a DO where in the iteration you
do match.

> --
> 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

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com