Sql UPDATE STATEMENT oracle

Question

I need to update a quantity in to my database. I tried with a update statement, (trigger oracle, don't work because definitely something wrong)

My sql string is:

String sql1 = "update book set qty = qty - 1 where isbn='" + txt_carr.getText() + "'";

txt_carr is a txtField where I write a isbn (for add a cart), but the quantity don't change for that precise ISBN.

Also in the cart when you close the program, should update the quantity (even at a fixed, for example 5)

I tried this:

String sql3= "UPDATE book SET quantity= 5 (select isbn cart where isbn=) ";

Where isbn = to all isbn in your cart. So it should only update the quantity (in table book) of the books in the shopping cart and not all.

I know that a trigger would work much better, but do not know how to write it.

So what is this being called from? Are you performing a commit? What if you take your update string and run it from sqlplus or other sql client? — OldProgrammer
– OldProgrammer, Commented Nov 17, 2014 at 12:41
@OldProgrammer The trigger for me is difficult. that's why I just wanted to do an update of a sql command. It's possible? — nonickname
– nonickname, Commented Nov 17, 2014 at 12:45
@GordonLinoff Write error on the forum. the query is correct. but still does not work. update book set qty = qty - 1 where isbn='" + txt_carr.getText() + "'"; why don't update the quantity? — nonickname
– nonickname, Commented Nov 17, 2014 at 13:09

Jay Ehsaniara · Accepted Answer · 2014-11-17 13:30:14Z

1

Try this one:

UPDATE book b   SET b.quantity =
      (SELECT COUNT (*)
         FROM cards c
        WHERE c.isbn = b.isbn) WHERE b.isbn = 'YOUR_ISBN';

So your code will be as:

String sql1= "UPDATE book b SET b.quantity =(SELECT COUNT (*) FROM card c WHERE c.isbn = b.isbn) WHERE b.isbn = '" + txt_carr.getText() + "' ";

Not: I'm not sure about table names.

answered Nov 17, 2014 at 13:30

Jay Ehsaniara

1,5591 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thanks, i solved for set quantity=5. But the problem remains for the decrease in the amount based on the isbn inserted in txtField.

@neo999, where are you getting the amount you want subtracted from?

@neo999 can you post you error? may be you have ROWLOCK before COMMIT or you "txt_carr" is null or txt_carr.getText() needs to be checked

PERFECT!!! the problem is txt_carr because is null at that time, I solved very very simply!!! thank you

Jordan · Accepted Answer · 2014-11-17 12:47:54Z

1

The SQL syntax is not valid :

String sql3= "UPDATE book SET quantity= '5' where book.isbn = 'YOUR_ISBN'";

answered Nov 17, 2014 at 12:47

Jordan

792 bronze badges

"YOUR ISBN" Should take all the ISBN in table cart and restore the quantity in table book. how can I do? thanks