Received: from www-uk-05.digiserve.com ([195.172.23.63]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f05A4a788777 for ; Fri, 5 Jan 2001 05:04:36 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id KAA02573 for ; Fri, 5 Jan 2001 10:05:48 GMT Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 5CAFF2A01E for ; Fri, 5 Jan 2001 09:59:24 +0000 (GMT) Message-ID: <000f01c076fe$2e27b9a0$1001a8c0@archonet.com> From: "Richard Huxton" To: Subject: Non-procedural field merging? Date: Fri, 5 Jan 2001 09:59:20 -0000 MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200101/61 X-Sequence-Number: 722 I have two tables, foo and foo2: richardh=> select * from foo; a | b ---+----- 1 | xxx 1 | yyy richardh=> select * from foo2; c | d ---+--- 1 | And I would like to set d to 'xxxyyy' (i.e. merge entries from b). Of course the following doesn't work because the 'd' seen is the one from before the query starts. richardh=> update foo2 set d = d || foo.b from foo where foo.a=foo2.c; UPDATE 1 richardh=> select * from foo2; c | d ---+----- 1 | yyy Now - I can always solve the problem procedurally, merging the values in my application but I was wondering if any of the smarter people on the list have an SQL way of doing it (something with sub-queries?) PS - I realise I might get 'xxxyyy' or 'yyyxxx' without forcing an order but I don't actually care in this case. TIA - Richard Huxton