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


-- 
Adrian Klaver
adrian.klaver@gmail.com