Received: from trade-india.com (unknown [130.94.20.28]) by postgresql.org (Postfix) with SMTP id 70FD2475C4A for ; Thu, 11 Apr 2002 02:31:41 -0400 (EDT) Received: (qmail 29905 invoked from network); 11 Apr 2002 06:43:47 -0000 Received: from ptil-235-129-del.primus-india.net (HELO trade-india.com) (203.196.129.235) by 130.94.22.209 with SMTP; 11 Apr 2002 06:43:47 -0000 Message-ID: <3CB52E8B.A7103324@trade-india.com> Date: Thu, 11 Apr 2002 12:04:51 +0530 From: Rajesh Kumar Mallah X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en MIME-Version: 1.0 To: Jean-Luc Lachance Cc: Jeff Eckermann , PostgResql SQL Mailing List Subject: Re: Postgresql goes into recovery mode .... References: <20020410155128.61589.qmail@web20809.mail.yahoo.com> <3CB46888.503DC660@nsd.ca> Content-Type: multipart/alternative; boundary="------------0C56D649D7A1F09A35098E20" X-Archive-Number: 200204/126 X-Sequence-Number: 7391 --------------0C56D649D7A1F09A35098E20 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit jeff i feel the second subselect is required becoz *without* the where clause the query: UPDATE email_bank set userid=(select userid from t_a where email_id=email_bank.email_id) would have updated *all* records in email_bank (1 million) where as i want to update only 35 K in those which are in t_a so "exists" is reqd. i also feel UPDATE .. FROM is better Sql for this purpose which can be written as: update email_bank set userid=t_a.userid from t_a where t_a.email_id = email_bank.email_id ; the above also puts my SQL in abnormnal state. Yes Jean UPDATE .. FROM works in 7.1.3. acutally what i want to know is even if my SQL were wrong what is the way out from the recovery mode? and less importantly is my SQL really wrong? regds mallah. Jean-Luc Lachance wrote: > If I reacall, it (UPDATE ... FROM) does not work with 7.1. > I think it was added only in 7.2. > > way is the only way with 7.1. > > Bottom line Rajesh: you should upgrade to 7.2.1 > > JLL > > Jeff Eckermann wrote: > > > > I think the second subselect (in the EXISTS clause) is > > not necessary, as it will always return true if the > > where clause in the first subselect (in the SET > > clause) is satisfied. > > > > Another way to write it (untested): > > update email_bank set userid = t_a.userid > > where email_id = t_a.email_id; > > > > --- Rajesh Kumar Mallah > (consult mailing list ) --------------0C56D649D7A1F09A35098E20 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit  
jeff  i feel  the  second subselect is  required  becoz *without* the where clause the
query:

UPDATE  email_bank set userid=(select userid  from t_a where
email_id=email_bank.email_id)

would have updated *all* records in email_bank  (1 million) where as
i want to update only 35 K in those which are in t_a  so "exists" is reqd.

i also feel UPDATE .. FROM  is better Sql for this purpose which
can be written as:

update  email_bank set userid=t_a.userid from t_a
where t_a.email_id = email_bank.email_id ;
 

the above also puts  my SQL in  abnormnal state.

Yes Jean  UPDATE .. FROM works in 7.1.3.

acutally  what i want to know  is even if my SQL were wrong
what is the way out  from  the recovery mode? and less importantly
is my SQL really wrong?

regds
mallah.
 
 
 

Jean-Luc Lachance wrote:

If I reacall, it (UPDATE ... FROM) does not work with 7.1.
I think it was added only in 7.2.

 way is the only way with 7.1.

Bottom line Rajesh: you should upgrade to 7.2.1

JLL

Jeff Eckermann wrote:
>
> I think the second subselect (in the EXISTS clause) is
> not necessary, as it will always return true if the
> where clause in the first subselect (in the SET
> clause) is satisfied.
>
> Another way to write it (untested):
> update email_bank set userid = t_a.userid
> where email_id = t_a.email_id;
>
> --- Rajesh Kumar Mallah <mallah@trade-india.com>


 
(consult mailing list )
--------------0C56D649D7A1F09A35098E20--