Home » SQL & PL/SQL » SQL & PL/SQL » Problem in creating procedure (10g Database)
Problem in creating procedure [message #438426] Fri, 08 January 2010 23:14 Go to next message
allianz2010
Messages: 101
Registered: October 2009
Location: Pakistan
Senior Member

i want to create procedure for update record...

i have two tables and both tables have records and now i add new field is PURCHASE_PRICE in SALE_INVOICE_DETAIL

one table have following fields.

table Name: ITEM

item_pk
item_name
purchase_price
sale_price


second table name is SALE_INVOICE_DETAIL

sale_invoice_detail_pk
item_fk
purchase_price




Now i want to update purchase_price which is exsists in SALE_INVOICE_DETAIL

I WANT TO UPDATE ALL SALE_INVOICE_DETAIL.PURCHASE_PRICE BASE ON ITEM TABLE PURCHASE PRICE...
Re: Problem in creating procedure [message #438428 is a reply to message #438426] Fri, 08 January 2010 23:17 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Post whatever you have tried so far
Re: Problem in creating procedure [message #438430 is a reply to message #438426] Fri, 08 January 2010 23:19 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Problem in creating procedure [message #438434 is a reply to message #438426] Sat, 09 January 2010 00:21 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Your question is not so clear to me.

Quote:

Now i want to update purchase_price which is exsists in SALE_INVOICE_DETAIL

UPDATE SALE_INVOICE_DETAIL
SET PURCHASE_PRICE = ...
WHERE...


Quote:
I WANT TO UPDATE ALL SALE_INVOICE_DETAIL.PURCHASE_PRICE BASE ON ITEM TABLE PURCHASE PRICE...

???

regards,
Delna
Re: Problem in creating procedure [message #438438 is a reply to message #438426] Sat, 09 January 2010 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
update SALE_INVOICE_DETAIL d
set purchase_price = 
    (select purchase_price from ITEM i
     where i.item_pk = d.item_pk)
where exists (select null from ITEM i
              where i.item_pk = d.item_pk)
/

Regards
Michel

[Updated on: Sat, 09 January 2010 02:07]

Report message to a moderator

Re: Problem in creating procedure [message #438441 is a reply to message #438438] Sat, 09 January 2010 02:27 Go to previous messageGo to next message
allianz2010
Messages: 101
Registered: October 2009
Location: Pakistan
Senior Member

Thanks Brother for your reply....

actually i m new in oracle and programming field...
update SALE_INVOICE_DETAIL d
set purchase_price = 
    (select purchase_price from ITEM i
     where i.item_pk = d.item_pk)
where exists (select null from ITEM i
              where i.item_pk = d.item_pk)
/

this query is worked but i see little problem and this is that

i have 5000 record in sale_invoice_detail table and 1000 records have purchase_price but when i execute this query its update all records purchase_price and main problem is this that some purchase_price is now change...

now what can i change in this query that when i execute this query then those record not updated which have sale_invoice_detail.purchase_price.


[Updated on: Sat, 09 January 2010 02:29] by Moderator

Report message to a moderator

Re: Problem in creating procedure [message #438443 is a reply to message #438441] Sat, 09 January 2010 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
now what can i change in this query that when i execute this query then those record not updated which have sale_invoice_detail.purchase_price.

I don't understand what you mean with this.
Currently the query update all records of SALE_INVOICE_DETAIL that have an associated row in ITEM. Was not this the requirement?

Regards
Michel

[Updated on: Sat, 09 January 2010 02:32]

Report message to a moderator

Re: Problem in creating procedure [message #438444 is a reply to message #438426] Sat, 09 January 2010 02:32 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Add a line to the where clause to check if purchase_price is not null.

Re: Problem in creating procedure [message #438446 is a reply to message #438443] Sat, 09 January 2010 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you mean that only if sale_invoice_detail.purchase_price which have no value must be updated?
In this case:
update SALE_INVOICE_DETAIL d
set purchase_price = 
    (select purchase_price from ITEM i
     where i.item_pk = d.item_pk)
where exists (select null from ITEM i
              where i.item_pk = d.item_pk)
  and d.purchase_price is null
/

If you have a foreign key on SALE_INVOICE_DETAIL.item_pk referencing ITEM.item_pk then you don't need the EXISTS part and the statement becomes:
update SALE_INVOICE_DETAIL d
set purchase_price = 
    (select purchase_price from ITEM i
     where i.item_pk = d.item_pk)
where d.purchase_price is null
/


Regards
Michel

[Updated on: Sat, 09 January 2010 03:12]

Report message to a moderator

Re: Problem in creating procedure [message #438449 is a reply to message #438444] Sat, 09 January 2010 03:30 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Sat, 09 January 2010 08:32
Add a line to the where clause to check if purchase_price is not null.



Should have been:
Add a line to the where clause to check if purchase_price is null.

i.e. what Michel said - need coffee obviously.
Previous Topic: Calculate One Query against another
Next Topic: Triggers, autonumbers and primary keys
Goto Forum:
  


Current Time: Mon Sep 26 09:25:06 CDT 2016

Total time taken to generate the page: 0.12272 seconds