Horton

Thread

  1. Update ordered

    Andreas Joseph Krogh <andreak@officenet.no> — 2014-01-27T14:36:10Z

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

  2. Re: Update ordered

    Adrian Klaver <adrian.klaver@gmail.com> — 2014-01-27T14:56:12Z

    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.

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

  3. Re: Update ordered

    Andreas Joseph Krogh <andreak@officenet.no> — 2014-01-27T15:01:11Z

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

  4. Re: Update ordered

    Adrian Klaver <adrian.klaver@gmail.com> — 2014-01-27T15:23:56Z

    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;

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

  5. Re: Update ordered

    Andreas Joseph Krogh <andreak@officenet.no> — 2014-01-27T15:37:28Z

    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  

  6. Re: Update ordered

    Adrian Klaver <adrian.klaver@gmail.com> — 2014-01-27T15:48:58Z

    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

  7. Re: Update ordered

    Achilleas Mantzios <achill@matrix.gatewaynet.com> — 2014-01-27T15:49:10Z

    On 27/01/2014 16:36, 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.

I think that makes sense. When you UPDATE ... FROM an another relation, nothing is guaranteed
about the order of the from_list join. Therefore "order by created asc" in your CTE is not gonna
achieve much.

Your better write this as a procedure. (as you have already suggested)

> Any idea how to achive this?
> Thanks.
> --
> 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


-- 
Achilleas Mantzios
Head of IT DEV
IT DEPT
Dynacom Tankers Mgmt