From: Imtiaz. S. M <imtiaz_sm@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: Re: [SQL] SQL Problem
Date: Fri, 29 Oct 1999 18:48:29 +0530
Message-ID: <38199EA4.A3EBA049@yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: imtiaz_sm@yahoo.com
Subject: Re: Re: [SQL] SQL Problem
In-Reply-To: <38199EA4.A3EBA049@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox