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>