From: Tom Lane <tgl@sss.pgh.pa.us>
To: dev@kbsolutions.ch
Cc: pgsql-sql@postgresql.org
Subject: Re: update from select
Date: Mon, 29 Oct 2007 10:30:08 -0400
Message-ID: <5998.1193668208@sss.pgh.pa.us> (raw)
In-Reply-To: <0d5e01c81a0c$b0f67250$1601a8c0@kbsc1>
References: <0d5e01c81a0c$b0f67250$1601a8c0@kbsc1>
<dev@kbsolutions.ch> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, dev@kbsolutions.ch
Subject: Re: update from select
In-Reply-To: <5998.1193668208@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox