Re: getting rid of "Adding missing FROM-clause entry...."

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(at)postgresql(dot)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.