Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37638=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1W7oR0-0000Li-N2
 for pgsql-sql@arkaria.postgresql.org; Mon, 27 Jan 2014 15:49:14 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37638=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1W7oR0-00022f-25
 for pgsql-sql@arkaria.postgresql.org; Mon, 27 Jan 2014 15:49:14 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <adrian.klaver@gmail.com>) id 1W7oQy-00022V-Ql
 for pgsql-sql@postgresql.org; Mon, 27 Jan 2014 15:49:13 +0000
Received: from mail-pa0-x236.google.com ([2607:f8b0:400e:c03::236])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <adrian.klaver@gmail.com>) id 1W7oQr-0004Ci-Ur
 for pgsql-sql@postgresql.org; Mon, 27 Jan 2014 15:49:11 +0000
Received: by mail-pa0-f54.google.com with SMTP id fa1so6093183pad.27
 for <pgsql-sql@postgresql.org>; Mon, 27 Jan 2014 07:49:03 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:subject:references
 :in-reply-to:content-type:content-transfer-encoding;
 bh=97DlrpyTo/L1CygneiYeCFXJLFdmIw02Qa3vUeZWNA8=;
 b=z9cvJwto5TZjOjdrunQjSpw0vGxajiqFqSW1YSwdot2impovYRvez5BaOsvwxIInwV
 rEtn8y92ojAWWcjfsWiFbK7k7BHASiBfZg8XMeSf6FIfb6CV66hiNE/W3/NOD1+6bqwc
 jSzfxqanDbqtxt5DeSjJ/xWOw/ixarfSkvZrUP/bq6BT8Nk88R74bRRDYaBF+8EycKXe
 wR7iZMXhljuXs7M2AsD0ogPNCu7mKCErz5zj6q973TNC/SvIqGeQ1GzYAcZiDFOHBtAY
 UyWeEsqRwi7JB8aTYHkus9D7EwuJbemghm3eNbApqDp62UVN77DA3ypJg9CFsNjPBGF/
 IWQQ==
X-Received: by 10.68.172.196 with SMTP id be4mr30944900pbc.12.1390837741327;
 Mon, 27 Jan 2014 07:49:01 -0800 (PST)
Received: from panda.site (65-102-185-39.tukw.qwest.net. [65.102.185.39])
 by mx.google.com with ESMTPSA id qz9sm33068871pbc.3.2014.01.27.07.49.00
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 27 Jan 2014 07:49:00 -0800 (PST)
Message-ID: <52E67FEA.7060506@gmail.com>
Date: Mon, 27 Jan 2014 07:48:58 -0800
From: Adrian Klaver <adrian.klaver@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:24.0) Gecko/20100101 Thunderbird/24.2.0
MIME-Version: 1.0
To: Andreas Joseph Krogh <andreak@officenet.no>,
 pgsql-sql@postgresql.org
Subject: Re: Update ordered
References: <OfficeNetEmail.29.db58afa68192a99d.143d456e56b@prod2>
In-Reply-To: <OfficeNetEmail.29.db58afa68192a99d.143d456e56b@prod2>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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