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
am Mon, dem 29.10.2007, um 10:18:38 +0100 mailte [EMAIL PROTECTED] folgendes:
>
> WHERE table1.column1 = temp_table.column1;
table1.column1 and temp_table.column1 have the same type?
>
>
>
> The select by it?s own takes around 1 second. The Update is around 120?000
> rows. I got an index on
Hello
you use corelated subquery and that is slow for thausands rows. Use
PostgreSQL's extension
UPDATE table1 SET column2 = t,colum2,
FROM table2 t
WHERE table1.column1 = t.column1 and t.column4 is not null and ...
http://www.postgresql.org/docs/8.2/interactive/sql-update.html
Regards
Pav
<[EMAIL PROTECTED]> 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
Yes, both have varchar(50).
Query:
UPDATE owner SET picturemedium = dvds.picturemedium, title = dvds.title,
titleOrder = dvds.title, releasedate = CAST(dvds.releasedate AS date) FROM
(
SELECT DISTINCT
detail_dvd.asin,
detail_dvd.picturemedium,
detail_dvd.title,
2007/10/29, [EMAIL PROTECTED] <[EMAIL PROTECTED]>:
>
> Yes, both have varchar(50).
>
> Query:
>
> UPDATE owner SET picturemedium = dvds.picturemedium, title = dvds.title,
> titleOrder = dvds.title, releasedate = CAST(dvds.releasedate AS date) FROM
> (
> SELECT DISTINCT
> detail_dvd.
<[EMAIL PROTECTED]> writes:
> Hash Join (cost=10827.45..25950.05 rows=4906 width=1191) (actual
> time=586.251..2852.691 rows=111306 loops=1)
> ...
> Total runtime: 633548.404 ms
So you're worried about the wrong thing entirely. The query is taking
less than 3 seconds, which may be reasonable con
