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
