If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > need an update statements

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 12
need an update statements

please can any one make for me an update statemets for this select statement

I want to set a.quantity =

select (a.quantity) + (b.quantity)
from a,b
where a.a_id = b.a_id;

I have tried this statements below and it does not work :

update a
set a.quantity = (
select (a.quantity) + (b.quantity)
from a,b
where a.a_id = b.a_id);
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,570
>set a.quantity = (
above A.QUANTITY is a scalar; which mean it can only accept a single value

post results from SQL below
select count(*) from a,b where a.a_id = b.a_id

since UPDATE statement contains no WHERE clause, every row in table A gets changed
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 12
sorry I did not understand what you mean
please can you provide me an example of the code
Reply With Quote
  #4 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,980
Without a test case (which YOU should have provided), have a look at this; maybe it'll help you develop your own solution.
Code:
SQL> select * from a;

        ID   QUANTITY
---------- ----------
         1        100
         2        200
         3        300

SQL> select * from b;

        ID   QUANTITY
---------- ----------
         2       2000

SQL> update a set
  2    a.quantity = a.quantity + (select b.quantity
  3                               from b
  4                               where b.id = a.id
  5                              )
  6    where exists (select null
  7                  from b
  8                  where b.id = a.id
  9                 );

1 row updated.

SQL> select * from a;

        ID   QUANTITY
---------- ----------
         1        100
         2       2200
         3        300

SQL>
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 12
Thank you it works
but can we add a trigger or procedure ,
I want only to update if a.quantity < 20
please if you can provide me the code how it will be


thanks alot for helping
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,570
>I want only to update if a.quantity < 20

so add this to the WHERE clause
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 12
I tried this code for trigger bellow but it doesnt works

create or replace
TRIGGER add_quantity
AFTER INSERT or update
ON a
FOR EACH ROW
declare
quan a.quantity%type;

BEGIN
if ( quan < 20)
then
update a set
a.quantity = a.quantity + (select b.quantity
from b
where b.id = a.id)
where exists (select null
from b
where b.id = a.id );
end if;

end;


one error saving changes to table "SYSTEM"."b":
Row 4: ORA-04091: table SYSTEM.a is mutating, trigger/function may not see it

Last edited by new_db; 12-24-12 at 10:00. Reason: spilling
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,570
You can not issue SQL against table upon which the TRIGGER is based.

The UPDATE would cause the TRIGGER to fire which would issue an UPDATE,
which would cause the TRIGGER to fire which would issue another UPDATE & so on!
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 12
So, what can I do to fix it, should I use procedure that will have the update statatment inside it and trigger to fire it? if it correct can you provide me the code of the procedure and trigger.

THANKS
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,570
why are you obsessed with using a TRIGGER?
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 12
I don't know what I should use that why I am asking you. So can I use procedure to solve this issue
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,570
post simple SQL statement that does what you require
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 12
how to use simple SQL and my requirment is to use if stament before update action, becuase of A.quantity decrease and reach below than 20 so It must updated automaticaly A.quantity = A.quantity+b.quantity
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,570
Where a.quanity < 20
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 12
I want to be automaticaly updated not only if I run the update statements which contain Where a.quanity < 20.
beacuse the number of A.quantity is decreasing,if I have the update statement I have to run it manual

Last edited by new_db; 12-24-12 at 11:25. Reason: spill
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On