Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    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);

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,647
    >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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Dec 2012
    Posts
    12
    sorry I did not understand what you mean
    please can you provide me an example of the code

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,008
    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>

  5. #5
    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

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,647
    >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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  7. #7
    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 11:00. Reason: spilling

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,647
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  9. #9
    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

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,647
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  11. #11
    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. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,647
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  13. #13
    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

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,647
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  15. #15
    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 12:25. Reason: spill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •