agora inbox for pgsql-sql@postgresql.orghelp / color / mirror / Atom feed
Update ordered 7+ messages / 3 participants [nested] [flat]
* Update ordered @ 2014-01-27 14:36 Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 14:56 ` Re: Update ordered Adrian Klaver <adrian.klaver@gmail.com> 2014-01-27 15:49 ` Re: Update ordered Achilleas Mantzios <achill@matrix.gatewaynet.com> 0 siblings, 2 replies; 7+ messages in thread From: Andreas Joseph Krogh @ 2014-01-27 14:36 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update ordered 2014-01-27 14:36 Update ordered Andreas Joseph Krogh <andreak@officenet.no> @ 2014-01-27 14:56 ` Adrian Klaver <adrian.klaver@gmail.com> 2014-01-27 15:01 ` Re: Update ordered Andreas Joseph Krogh <andreak@officenet.no> 1 sibling, 1 reply; 7+ messages in thread From: Adrian Klaver @ 2014-01-27 14:56 UTC (permalink / raw) To: Andreas Joseph Krogh <andreak@officenet.no>; pgsql-sql 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update ordered 2014-01-27 14:36 Update ordered Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 14:56 ` Re: Update ordered Adrian Klaver <adrian.klaver@gmail.com> @ 2014-01-27 15:01 ` Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 15:23 ` Re: Update ordered Adrian Klaver <adrian.klaver@gmail.com> 0 siblings, 1 reply; 7+ messages in thread From: Andreas Joseph Krogh @ 2014-01-27 15:01 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update ordered 2014-01-27 14:36 Update ordered Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 14:56 ` Re: Update ordered Adrian Klaver <adrian.klaver@gmail.com> 2014-01-27 15:01 ` Re: Update ordered Andreas Joseph Krogh <andreak@officenet.no> @ 2014-01-27 15:23 ` Adrian Klaver <adrian.klaver@gmail.com> 2014-01-27 15:37 ` Re: Update ordered Andreas Joseph Krogh <andreak@officenet.no> 0 siblings, 1 reply; 7+ messages in thread From: Adrian Klaver @ 2014-01-27 15:23 UTC (permalink / raw) To: Andreas Joseph Krogh <andreak@officenet.no>; pgsql-sql 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update ordered 2014-01-27 14:36 Update ordered Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 14:56 ` Re: Update ordered Adrian Klaver <adrian.klaver@gmail.com> 2014-01-27 15:01 ` Re: Update ordered Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 15:23 ` Re: Update ordered Adrian Klaver <adrian.klaver@gmail.com> @ 2014-01-27 15:37 ` Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 15:48 ` Re: Update ordered Adrian Klaver <adrian.klaver@gmail.com> 0 siblings, 1 reply; 7+ messages in thread From: Andreas Joseph Krogh @ 2014-01-27 15:37 UTC (permalink / raw) To: pgsql-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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update ordered 2014-01-27 14:36 Update ordered Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 14:56 ` Re: Update ordered Adrian Klaver <adrian.klaver@gmail.com> 2014-01-27 15:01 ` Re: Update ordered Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 15:23 ` Re: Update ordered Adrian Klaver <adrian.klaver@gmail.com> 2014-01-27 15:37 ` Re: Update ordered Andreas Joseph Krogh <andreak@officenet.no> @ 2014-01-27 15:48 ` Adrian Klaver <adrian.klaver@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: Adrian Klaver @ 2014-01-27 15:48 UTC (permalink / raw) To: Andreas Joseph Krogh <andreak@officenet.no>; pgsql-sql 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update ordered 2014-01-27 14:36 Update ordered Andreas Joseph Krogh <andreak@officenet.no> @ 2014-01-27 15:49 ` Achilleas Mantzios <achill@matrix.gatewaynet.com> 1 sibling, 0 replies; 7+ messages in thread From: Achilleas Mantzios @ 2014-01-27 15:49 UTC (permalink / raw) To: pgsql-sql 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2014-01-27 15:49 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2014-01-27 14:36 Update ordered Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 14:56 ` Adrian Klaver <adrian.klaver@gmail.com> 2014-01-27 15:01 ` Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 15:23 ` Adrian Klaver <adrian.klaver@gmail.com> 2014-01-27 15:37 ` Andreas Joseph Krogh <andreak@officenet.no> 2014-01-27 15:48 ` Adrian Klaver <adrian.klaver@gmail.com> 2014-01-27 15:49 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox