Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 812AA9FA070 for ; Mon, 29 Oct 2007 11:30:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 11176-06 for ; Mon, 29 Oct 2007 11:30:11 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 87D859F9F65 for ; Mon, 29 Oct 2007 11:30:11 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l9TEU8pf005999; Mon, 29 Oct 2007 10:30:08 -0400 (EDT) To: dev@kbsolutions.ch cc: pgsql-sql@postgresql.org Subject: Re: update from select In-reply-to: <0d5e01c81a0c$b0f67250$1601a8c0@kbsc1> References: <0d5e01c81a0c$b0f67250$1601a8c0@kbsc1> Comments: In-reply-to message dated "Mon, 29 Oct 2007 10:18:38 +0100" Date: Mon, 29 Oct 2007 10:30:08 -0400 Message-ID: <5998.1193668208@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/145 X-Sequence-Number: 29517 writes: > Is there a better way to do this update: > UPDATE table1 SET column2 = temp_table.column2, column3 = > temp_table.column3, column4 = CAST(temp_table.column4 AS date) FROM > ( > SELECT DISTINCT > table2.column1, > table2.column2, > table2.column3, > table2.column4 > FROM table2 WHERE column4 IS NOT NULL AND column4 <> '' AND > (length(column4) = 10 OR length(column4) = 23) > ) AS temp_table > WHERE table1.column1 = temp_table.column1; This looks seriously fishy. Is table2.column1 unique? If it is then you don't need the DISTINCT. If it isn't, you are in great danger of trying to update (some) table1 rows multiple times; which is bad, both because it wastes cycles and because you have no idea which of the matching table2 rows will "win" the update. I think you first need to think clearly about what you're doing ... regards, tom lane