Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W7oFn-0008Nb-R9 for pgsql-sql@arkaria.postgresql.org; Mon, 27 Jan 2014 15:37:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1W7oFn-0007Fo-Aj for pgsql-sql@arkaria.postgresql.org; Mon, 27 Jan 2014 15:37:39 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W7oFm-0007Ff-5l for pgsql-sql@postgresql.org; Mon, 27 Jan 2014 15:37:38 +0000 Received: from prod2.officenet.no ([195.159.87.246]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W7oFe-0003yd-Sm for pgsql-sql@postgresql.org; Mon, 27 Jan 2014 15:37:37 +0000 Received: from localhost ([127.0.0.1] helo=prod2) by prod2.officenet.no with esmtp (Exim 4.76) (envelope-from ) id 1W7oFc-0000n3-F7 for pgsql-sql@postgresql.org; Mon, 27 Jan 2014 16:37:28 +0100 Date: Mon, 27 Jan 2014 16:37:28 +0100 (CET) From: Andreas Joseph Krogh To: pgsql-sql@postgresql.org Message-ID: In-Reply-To: <52E67A0C.5020605@gmail.com> Subject: Re: Update ordered MIME-Version: 1.0 X-Mailer: OfficeNet Mail 1.9.0-SNAPSHOT X-Pg-Spam-Score: -2.4 (--) Content-Type: multipart/alternative; boundary="----=_Part_102_803687939.1390837048395" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_100_122078849.1390837048383 Content-Type: multipart/related; boundary="----=_Part_101_932332258.1390837048383" ------=_Part_101_932332258.1390837048383 Content-Type: multipart/alternative; boundary="----=_Part_102_803687939.1390837048395" ------=_Part_102_803687939.1390837048395 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 mandag 27. januar 2014 kl. 16:23:56, skrev Adrian Klaver < adrian.klaver@gmail.com >: On 01/27/2014 07= :01=20 AM, Andreas Joseph Krogh wrote: > P=C3=A5 mandag 27. januar 2014 kl. 15:56:12, skrev Adrian Klaver > >: > >=C2=A0 =C2=A0 =C2=A0On 01/27/2014 06:36 AM, Andreas Joseph Krogh wrote: >=C2=A0 =C2=A0 =C2=A0 > Hi all. >=C2=A0 =C2=A0 =C2=A0 > I want an UPDATE query to update my project's proj= ect_number in >=C2=A0 =C2=A0 =C2=A0 > chronological order (according to the project's "c= reated"-column) and >=C2=A0 =C2=A0 =C2=A0 > tried this: >=C2=A0 =C2=A0 =C2=A0 > with upd as( >=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 select id from project order b= y created asc >=C2=A0 =C2=A0 =C2=A0 > ) update project p set project_number =3D get_next= _project_number() >=C2=A0 =C2=A0 =C2=A0from >=C2=A0 =C2=A0 =C2=A0 > upd where upd.id =3D p.id; >=C2=A0 =C2=A0 =C2=A0 > However, the olders project doesn't get the smalle= s project_number. >=C2=A0 =C2=A0 =C2=A0 > Any idea how to achive this? > >=C2=A0 =C2=A0 =C2=A0That would seem to depend on what get_next_project_nu= mber() does, the >=C2=A0 =C2=A0 =C2=A0contents 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( =C2=A0 =C2=A0 =C2=A0select id from project order by created asc ) select p.id, p.create from project_number where upd.id =3D p.id; =C2=A0 = Yes, that=20 returns ordered result, but the update CTE doens't update with the oldest= =20 project getting the first sequenc-nr. =C2=A0 Using a DO statement, iteratin= g over=20 all projects ordered by "created" then updating each project matching the= =20 current iteration works, but I'd like to be able to do it in one statement = as=20 I'm sure it's possible... =C2=A0 -- Andreas Joseph Krogh =C2=A0 =C2=A0 =C2=A0 mob: +47 9= 09 56 963 Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no Public key: http://home.officenet.no/~andreak/public_key.asc =C2=A0 ------=_Part_102_803687939.1390837048395 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 mandag 27. januar 2014 kl. 16:23:56, skrev Adrian Klaver <<= a href=3D"mailto:adrian.klaver@gmail.com" target=3D"_blank">adrian.klaver@g= mail.com>:
On = 01/27/2014 07:01 AM, Andreas Joseph Krogh wrote:
> P=C3=A5 mandag 27. januar 2014 kl. 15:56:12, skrev Adrian Klaver
> <adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.com>>= :
>
>=C2=A0 =C2=A0 =C2=A0On 01/27/2014 06:36 AM, Andreas Joseph Krogh wrote:=
>=C2=A0 =C2=A0 =C2=A0 > Hi all.
>=C2=A0 =C2=A0 =C2=A0 > I want an UPDATE query to update my project's= project_number in
>=C2=A0 =C2=A0 =C2=A0 > chronological order (according to the project= 's "created"-column) and
>=C2=A0 =C2=A0 =C2=A0 > tried this:
>=C2=A0 =C2=A0 =C2=A0 > with upd as(
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 select id from project or= der by created asc
>=C2=A0 =C2=A0 =C2=A0 > ) update project p set project_number =3D get= _next_project_number()
>=C2=A0 =C2=A0 =C2=A0from
>=C2=A0 =C2=A0 =C2=A0 > upd where upd.id =3D p.id;
>=C2=A0 =C2=A0 =C2=A0 > However, the olders project doesn't get the s= malles project_number.
>=C2=A0 =C2=A0 =C2=A0 > Any idea how to achive this?
>
>=C2=A0 =C2=A0 =C2=A0That would seem to depend on what get_next_project_= number() does, the
>=C2=A0 =C2=A0 =C2=A0contents 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 havi= ng
> 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(
=C2=A0 =C2=A0 =C2=A0select id from project order by created asc
) select p.id, p.create from project_number where upd.id =3D p.id;
=C2=A0
Yes, that returns ordered result, but the update CTE doens't update wi= th the oldest project getting the first sequenc-nr.
=C2=A0
Using a DO statement, iterating over all projects ordered by "cre= ated" 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 possibl= e...
=C2=A0
--
Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 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
=C2=A0
------=_Part_102_803687939.1390837048395-- ------=_Part_101_932332258.1390837048383-- ------=_Part_100_122078849.1390837048383--