update from select - Mailing list pgsql-sql

From
Subject update from select
Date
Msg-id 0d5e01c81a0c$b0f67250$1601a8c0@kbsc1
Whole thread Raw
Responses Re: update from select
Re: update from select
Re: update from select
List pgsql-sql
<div class="Section1"><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB" style="font-size:
9.0pt;font-family:Verdana">Hello</span></font><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB"
style="font-size:
9.0pt;font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB"
style="font-size:
9.0pt;font-family:Verdana">I have a performance problem with an SQL statement.</span></font><p class="MsoNormal"><font
face="Verdana"size="1"><span lang="EN-GB" style="font-size: 
9.0pt;font-family:Verdana">Is there a better way to do this update:</span></font><p class="MsoNormal"><font
face="Verdana"size="1"><span lang="EN-GB" style="font-size: 
9.0pt;font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB"
style="font-size:
9.0pt;font-family:Verdana">UPDATE table1 SET column2 = temp_table.column2, column3 = temp_table.column3, column4 =
CAST(temp_table.column4AS date) FROM</span></font><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB"
style="font-size:
9.0pt;font-family:Verdana">(</span></font><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB"
style="font-size:
9.0pt;font-family:Verdana"> SELECT DISTINCT</span></font><p class="MsoNormal"><font face="Verdana" size="1"><span
lang="EN-GB"style="font-size: 
9.0pt;font-family:Verdana"> table2.column1,</span></font><p class="MsoNormal"><font face="Verdana" size="1"><span
lang="EN-GB"style="font-size: 
9.0pt;font-family:Verdana"> table2.column2,</span></font><p class="MsoNormal"><font face="Verdana" size="1"><span
lang="EN-GB"style="font-size: 
9.0pt;font-family:Verdana"> table2.column3,</span></font><p class="MsoNormal"><font face="Verdana" size="1"><span
lang="EN-GB"style="font-size: 
9.0pt;font-family:Verdana"> table2.column4</span></font><p class="MsoNormal"><font face="Verdana" size="1"><span
lang="EN-GB"style="font-size: 
9.0pt;font-family:Verdana"> FROM table2 WHERE column4 IS NOT NULL AND column4 <> '' AND (length(column4) = 10 OR
length(column4)= 23) </span></font><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB"
style="font-size:
9.0pt;font-family:Verdana">) AS temp_table</span></font><p class="MsoNormal"><font face="Verdana" size="1"><span
lang="EN-GB"style="font-size: 
9.0pt;font-family:Verdana">WHERE table1.column1 = temp_table.column1;</span></font><p class="MsoNormal"><font
face="Verdana"size="1"><span lang="EN-GB" style="font-size: 
9.0pt;font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB"
style="font-size:
9.0pt;font-family:Verdana">The select by it’s own takes around 1 second. The Update is around 120’000 rows. I got an
indexon column1. The whole query needs around 16 minutes.</span></font><p class="MsoNormal"><font face="Verdana"
size="1"><spanlang="EN-GB" style="font-size: 
9.0pt;font-family:Verdana">The same procedure on MSSQL needs around 30 seconds. I hope to get it too in
Postgres…</span></font><pclass="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB" style="font-size: 
9.0pt;font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB"
style="font-size:
9.0pt;font-family:Verdana">Please help me.</span></font><p class="MsoNormal"><font face="Verdana" size="1"><span
lang="EN-GB"style="font-size: 
9.0pt;font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB"
style="font-size:
9.0pt;font-family:Verdana">Regards</span></font><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB"
style="font-size:
9.0pt;font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="1"><span lang="EN-GB"
style="font-size:
9.0pt;font-family:Verdana">Reto</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:
12.0pt"> </span></font></div>

pgsql-sql by date:

Previous
From: Paul Lambert
Date:
Subject: Re: Select into with dynamic criteria in a plpgsql function
Next
From: "A. Kretschmer"
Date:
Subject: Re: update from select