increment the row value by one using update
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: increment the row value by one using update

  1. #1
    Join Date
    Dec 2000
    Posts
    95

    Question

    I have requirement like this

    i have a table containing three rows

    question agree disagree
    Q1 2 1
    Q2 6 2

    if i need to update the row where Question=Q1
    if the answer is agree i have to update the agree column value to 3 , if the answer is disagree the i have to update disagree column to 2.

    for this what i am doing is fisrt i am retrieving the the values of of the Q1 row by using select statement.
    and then i am updating the the agree or disagree row depending upon the answer.

    My problem is i am making two trips to database one is select and then update , instead of that can i update the row using only update statement ?

    i am using JDBC,oracle8i.

    thanks for any help.

    SrinivasM





  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    UPDATE my_table
    SET agree = DECODE(:answer, 'agree', agree+1, agree),
    disagree = = DECODE(:answer, 'disagree', disagree+1, disagree)
    WHERE question = 'Q1';
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2000
    Posts
    95

    Question increment the row value by one using update

    Hi Jmodic,

    i have tried the way you specified. but the thing is answer is not a column in my table. I think normally in decode we compare a column to a value. But in my case answer is not a column. That value is coming as a request from another JSP page.

    ok when i tried the query which you specified its throwing the folowing error.

    SQL> Update survey set agree = Decode(:agree,2,agree+1,agree) where question='q1';

    SP2-0552: Bind variable "AGREE" not declared.

    this is my table
    SQL> select * from survey;

    QUESTION AGREE DISAGREE NONE
    ---------- --------- --------- ---------
    q1 2 0 0
    q2 0 0 0
    q3 0 0 0

    3 rows selected.


    My question is how we can Bind a variable ?

    i am using jdbc,oracle8i

    thanks for any help

    SrinivasM

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    In DECODE you are comparing one expression (or a value) to another. It doesn't have to be a column at all.

    In my example I've used a bind variable ":answer" as one of the DECODE parameters. It doesn't have to be a bind variable, it can also be a literal value (string) or a variable that holds that value.

    I don't have a clue about JSP and JDBC, but I suppose in your program you are storing the answer in some kind of variable. Use this instead of the :answer bind variable.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width