| |
|
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.
|
 |
|
12-23-12, 17:25
|
|
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);
|
|
12-23-12, 17:54
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,091
|
|
>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.
|
|
12-24-12, 03:51
|
|
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
|
|
12-24-12, 04:12
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,848
|
|
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>
|
|
12-24-12, 08:17
|
|
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
|
|
12-24-12, 09:21
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,091
|
|
>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.
|
|
12-24-12, 09:58
|
|
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
|
12-24-12, 10:03
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,091
|
|
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.
|
|
12-24-12, 10:28
|
|
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
|
|
12-24-12, 10:46
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,091
|
|
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.
|
|
12-24-12, 10:52
|
|
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
|
|
12-24-12, 10:57
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,091
|
|
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.
|
|
12-24-12, 11:12
|
|
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
|
|
12-24-12, 11:15
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,091
|
|
|
__________________
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.
|
|
12-24-12, 11:24
|
|
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
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
need an update statements
