Re: Re: [SQL] SQL Problem

First of all thanks to both Peter Eisentraut and Fabio Ambrosanio for
their help. I still haven't been able to fix the problem I'm facing.

My problem was

> I have two tables which have exactly the same structure. Both of them
> have one datetime column along with three other columns. But the
> datetime column value in the second table is NULL for all the rows.
> Now I want to select the least date(date with the least value in the
>datetime column) from the first table and update all the rows in the
second table
> with this least date value from the first table. I do not want to use
> any compiler or host variables. I should be able to do this only using

> SQL statements. Can somebody please help me? Thanks in advance.
>
> regards
> Imtiaz

Peter Eisentraut wrote

UPDATE second_table SET date_column = (SELECT min(date_column) FROM
first_table);

But this give me the following error

ERROR:  parser:  parse error at or near select

Then Fabio Ambrosanio wrote

update second_table set datetime_field =
min(first_table.datetime_field)
from first_table;

This works for smaller tables alright.
But my first table contains about 100000 rows and the second table
contains about 9000 rows
After submitting the query for execution, it takes a very long time and
then gives the following error.

FATAL 1:  Memory exhausted in AllocSetAlloc()
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is
impossible.  Terminating.

I have even tried increasing the size of the shared memory. I think this
might be an optimization problem.
I'm using a PII 350Mhz machine(i686) with 32 Mb Ram and 4gb hard disk.
My OS is Linux Kernel v 2.0.36 Red Hat v 5.2
PostgreSQL version is 6.5.1
I would also prefer the SQL statement which solves this prolem to be
SQL92 compliant.

Can somebody solve this please?

regards
Imtiaz