Re: update from select

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

<dev(at)kbsolutions(dot)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