Decreasing numbers in SQL

[shuzo monsoon]

Hello! What I'm trying to do at the moment is have a query which finds the appropriate record of a particulat product in the STOCK table and then decrement that quantity by 1. However, I'm not sure ntirely how to do this, as I know how to change the number by specifying one but I'm not sure how to minus the number. I tried this :

stmt.executeUpdate("UPDATE STOCK SET QuantityInStock='"+ QuantityInStock-1 +"' WHERE Cid = '"+ cid +"'");

But this doesn't work because the second QuantityInStock is not recognised. Has anyone any ideas?
Thanks!

[Blake Minghelli]

If the STCOK table has a column called "QuantityInStock" then you can do this:

stmt.executeUpdate(
  "UPDATE STOCK SET QuantityInStock = (QuantityInStock - 1) " + 
  "WHERE Cid = '"+ cid +"'"
);  

[eammon bannon]

But this doesn't work because the second QuantityInStock is not recognised
[/qoute]
Can you explain what you mean by this? Are you getting a SQLException, or is the variable undefined? It looks like it should work fine to me, though you appear to probably be using a SQL varchar datatype for QuantityInStock, despite treating it as numeric.

[shuzo monsoon]

Thanks Blake! That worked. Thanks!