Re: getting rid of "Adding missing FROM-clause entry...." - Mailing list pgsql-sql

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.idorder 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,
mname2character 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.