On 16/08/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>
> --- Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>
> > On 16/08/07, Rodrigo De León <rdeleonp@gmail.com> wrote:
> > > On Aug 15, 11:46 pm, phoenix.ki...@gmail.com ("Phoenix Kiula") wrote:
> > > > Appreciate any tips, because it would
> > > > be nasty to have to do this with millions of UPDATE statements!
> > >
> > > - Create an interim table
> > > - COPY the data into it
> > > - Do an UPDATE ... FROM ...
> >
> >
> > Thanks! I thought about it and then gave up because SQL trumped me up.
> > Could you please suggest what the query should look like?
> >
> > Based on this:
> > http://www.postgresql.org/docs/8.1/static/sql-update.html
> >
> > I tried this:
> >
> > UPDATE
> > t1 SET title = title FROM t2
> > WHERE
> > t1.id = t2.id;
>
>
> UPDATE T1
> SET T1.title = T2.title
> FROM T2
> WHERE T1.id = T2.id
> AND T1.title IS NULL;
>
> or
>
> UPDATE T1
> SET title = ( SELECT title
> FROM T2
> WHERE T2.id = T1.id )
> WHERE T1.title IS NULL;
Thanks much RIchard, but neither of those work. For me table t1 has
over 6 million rows, and table t2 has about 600,000. In both of the
queries above I suppose it is going through each and every row of
table t1 and taking its own sweet time. I've dropped all indexes on
t1, but the query has still been running for over 45 minutes as I
write! Any other suggestions?
