From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Subject: Re: getting rid of "Adding missing FROM-clause entry...."
Date: Thu, 17 Apr 2003 20:21:28 +0530
Message-ID: <200304172021.28073.mallah@trade-india.com> (raw)
In-Reply-To: <3E9EB59F.B01FBF60@rodos.fzk.de>
References: <3E9EB59F.B01FBF60@rodos.fzk.de>
On Thursday 17 Apr 2003 7:39 pm, Christoph Haller wrote:
> > > And why are you using "limit 1 offset 0"?
> >
> > becuase the subquery results in mutiple rowss.
> > I want the first row to update title1,fname1,mname1,lname1,degs1
> >
> > and use the second row by using "limit 1 offset 1" to
> > update title2,fname2,mname2,lname2,degs2 etc
> >
> > do u still want me to try the query u gave?
> > (actually i did try and its giving some trivial error)
>
> No. Looks like a misunderstanding.
> Do I get it right this time assuming you want to
> set up multiple update commands which update
> only one row at a time?
> Regards, Christoph
Dear Chris ,
Thanks so much for ur interest.
I have prepared a prototype for this
problem and including the SQL.
actually my orignal problem is to update
10 *columns* in profile master first five comes
from first entry in another table and remaining
5 columns comes from the second corresponding
row in the other table. the two rows in the
second columns have the same company_id but
different contact_id. sort of denormalizing
excercise.
this needs to be done for all the companies
in 2 seperate update commands(or better 1).
Eg: below is a prototype for the problem.
(SQL are included below)
Table t_b
+----+--------+-------+-------+
| id | con_id | fname | mname |
+----+--------+-------+-------+
| 1 | 1 | X | Y |
| 1 | 2 | U | V |
| 2 | 1 | S | T |
+----+--------+-------+-------+
shud be translated to
+----+--------+--------+--------+--------+
| id | fname1 | mname1 | fname2 | mname2 |
+----+--------+--------+--------+--------+
| 1 | X | Y | U | V |
| 2 | S | T | NULL | NULL |
+----+--------+--------+--------+--------+
but
UPDATE t_a set fname1=foo.fname1 , mname1=foo.mname1 FROM
(select fname as fname1 , mname as mname1 from t_b where id=t_a.id
order by con_id limit 1 offset 0) as foo ;
gives:
NOTICE: Adding missing FROM-clause entry in subquery for table "t_a"
and updates wrongly.
tradein_clients=# SELECT * from t_a;
+----+--------+--------+--------+--------+
| id | fname1 | mname1 | fname2 | mname2 |
+----+--------+--------+--------+--------+
| 1 | X | Y | NULL | NULL |
| 2 | X | Y | NULL | NULL |
+----+--------+--------+--------+--------+
(2 rows)
// SQL BEGINS.
CREATE TABLE t_b (
id integer,
con_id integer,
fname character varying,
mname character varying
);
INSERT INTO t_b (id, con_id, fname, mname) VALUES (1, 1, 'X', 'Y');
INSERT INTO t_b (id, con_id, fname, mname) VALUES (1, 2, 'U', 'V');
INSERT INTO t_b (id, con_id, fname, mname) VALUES (2, 1, 'S', 'T');
CREATE TABLE t_a (
id integer,
fname1 character varying,
mname1 character varying,
fname2 character varying,
mname2 character varying
);
INSERT INTO t_a (id, fname1, mname1, fname2, mname2) VALUES (1, 'X', 'Y', NULL, NULL);
INSERT INTO t_a (id, fname1, mname1, fname2, mname2) VALUES (2, 'X', 'Y', NULL, NULL);
// SQL ENDS.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
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: mallah@trade-india.com, ch@rodos.fzk.de
Subject: Re: getting rid of "Adding missing FROM-clause entry...."
In-Reply-To: <200304172021.28073.mallah@trade-india.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