update from select

[<dev(at)kbsolutions(dot)ch>]

Hello

 

I have a performance problem with an SQL statement.

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;

 

The select by it’s own takes around 1 second. The Update is around 120’000 rows. I got an index on column1. The whole query needs around 16 minutes.

The same procedure on MSSQL needs around 30 seconds. I hope to get it too in Postgres…

 

Please help me.

 

Regards

 

Reto