Home » SQL & PL/SQL » SQL & PL/SQL » Updating one table from other two tables (Oracle8i WindowXP)
Updating one table from other two tables [message #341095] Sat, 16 August 2008 06:42 Go to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Dear Coleagues,

First of all I thankfull to all of you because I have learnt so much about oracle which was not available in the books I have. I beleave that God helps those who helps others.

Now I am facing a problem in the below update statement which seems to be correct but does not execute and appear the following error.

update vrd set 
  2  (vdiname, vdqty) = (select i.iname,id.invdqtysal
  3  from item i,invd id 
  4  where i.icode = id.invdicode
  5  and id.invdvrtype = 'SI')
  6  where vdinvhno = id.invhno
  7  and vdinvdno = id.invdno;
and vdinvdno = id.invdno
                  *
ERROR at line 7:
ORA-00904: invalid column name

Whereas the name of the culumn is correct in the table. Please help me.
Re: Updating one table from other two tables [message #341096 is a reply to message #341095] Sat, 16 August 2008 06:45 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Post a describe of your invd table. And please refrain from making religious references in your posts. A technical forum is not the place for it and it can offend people.
Re: Updating one table from other two tables [message #341101 is a reply to message #341095] Sat, 16 August 2008 08:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
id.invdno is outside the scope, because you have placed it outside the parentheses, instead of inside the parentheses where it belongs.
Re: Updating one table from other two tables [message #341109 is a reply to message #341101] Sat, 16 August 2008 10:12 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thanks for your quick response. The invd table structure is as under:
SQL> desc invd
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 INVHNO                                    NOT NULL NUMBER(10)
 INVDVRTYPE                                         VARCHAR2(2)
 INVDVRDATE                                         DATE
 INVDVRNO                                           VARCHAR2(4)
 INVDCRTERM                                         NUMBER(3)
 INVDNO                                    NOT NULL NUMBER(4)
 INVDBCODE                                          VARCHAR2(5)
 INVDILCODE                                         VARCHAR2(1)
 INVDIMCODE                                         VARCHAR2(3)
 INVDIWCODE                                         VARCHAR2(3)
 INVDICODE                                          VARCHAR2(6)
 INVDLCODE                                          VARCHAR2(5)
 INVDQTYPUR                                         NUMBER(5)
 INVDQTYPR                                          NUMBER(5)
 INVDQTYSAL                                         NUMBER(5)
 INVDQTYSR                                          NUMBER(5)
 INVDRATE                                           NUMBER(8,2)
 INVDTAMT                                           NUMBER(10,2)
 INVDDISPER                                         NUMBER(6,2)
 INVDDISAMT                                         NUMBER(8,2)
 INVDNETAMT                                         NUMBER(10,2)
 INVDSITE                                           VARCHAR2(15)
 INVDPARTICULAR                                     VARCHAR2(65)
 INVDFREIGHT                                        NUMBER(6)
 INVDQTYORD                                         NUMBER(5)
 INVDCLCODE                                         VARCHAR2(1)
 INVDCMCODE                                         VARCHAR2(3)
 INVDCWCODE                                         VARCHAR2(3)
Re: Updating one table from other two tables [message #341112 is a reply to message #341095] Sat, 16 August 2008 10:23 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
please focus on Barbara's response
Re: Updating one table from other two tables [message #341113 is a reply to message #341101] Sat, 16 August 2008 10:32 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thanks Barbara Boehmer. id invhno and id.invdno is a composit primary key in the table invd and its composit foreign key is vdinvhno and vdinvdno in the table vrd. The describe of vrd table and item table are as under:
SQL> desc vrd
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 VDTYPE                                             VARCHAR2(2)
 VDDATE                                             DATE
 VDVHNO                                             VARCHAR2(4)
 VDCRTERM                                           NUMBER(3)
 VDTCODE                                            VARCHAR2(6)
 VDSNO                                     NOT NULL NUMBER(20)
 VDLCODE                                            VARCHAR2(1)
 VDMCODE                                            VARCHAR2(3)
 VDWCODE                                            VARCHAR2(3)
 VDPARTICULAR                                       VARCHAR2(65)
 VDDR                                               NUMBER(13,2)
 VDCR                                               NUMBER(13,2)
 VDORDSBRNO                                         VARCHAR2(10)
 VDORDSBRDATE                                       DATE
 VDORDASNO                                          NUMBER(10)
 VDORDISNO                                          NUMBER(10)
 VDINVHNO                                           NUMBER(10)
 VDINVDNO                                           NUMBER(4)
 VDINVOTHNO                                         NUMBER(10)
 VDQTY                                              NUMBER(5)
 VDRATE                                             NUMBER(8,2)
 VDFREIGHT                                          NUMBER(6)
 VDBCODE                                            VARCHAR2(5)
 VDBILLNO                                           NUMBER(4)
 VDBILLTAG                                          VARCHAR2(1)
 VDINAME                                            VARCHAR2(30)

SQL> desc item
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 IBCODE                                             VARCHAR2(5)
 ICODE                                     NOT NULL VARCHAR2(6)
 INAME                                              VARCHAR2(30)
 ILCODE                                             VARCHAR2(1)
 IMCODE                                             VARCHAR2(3)
 IWCODE                                             VARCHAR2(3)
 IOPQTY                                             NUMBER(5)
 IOPRATE                                            NUMBER(8,2)
 IOPAMT                                             NUMBER(10,2)
Re: Updating one table from other two tables [message #341114 is a reply to message #341113] Sat, 16 August 2008 10:38 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
please re-read anacedent's response
Re: Updating one table from other two tables [message #341119 is a reply to message #341095] Sat, 16 August 2008 11:58 Go to previous message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thanks to all specially Barbara Boehmer, pablolee and anacedent.
I have solved my problem. The correct query is as under it may help others.
SQL> update vrd vr set 
  2  (vdiname, vdqty) = (select i.iname,id.invdqtysal from brand b,item i,invd id, invh ih 
  3       where ih.invhvrtype = 'SI'
  4       and id.invhno = ih.invhno
  5       and b.bcode = i.ibcode
  6       and i.icode = id.invdicode
  7       and i.ibcode = id.invdbcode
  8       and id.invdvrtype = vr.vdtype
  9       and id.invdvrno = vr.vdvhno
 10       and id.invdvrdate = vr.vddate
 11       and id.invhno = vr.vdinvhno
 12       and id.invdno = vr.vdinvdno)
 13  where vr.vdtype = 'SI';

942 rows updated.
Previous Topic: How to write this query?
Next Topic: can some explain me in detailed how the below query works
Goto Forum:
  


Current Time: Fri Dec 09 15:25:08 CST 2016

Total time taken to generate the page: 0.32806 seconds