Decrement field value by 1, using a single query

[danb]

Hi,

I have a product table in Access, like this:

product_id | product_name | product_quantity



I need to be able to do an SQL update on this table to subtract 1 from the product_quantity field.

I could do this with two SQL queries (SELECT product_quantity, then subtract 1, then UPDATE table) but I'm sure there must be a way just to run one query which subtracts 1 from the field.

Does anyone know how to do this?

Any help would be fantastic, thanks!

 

[reclusivemonkey]

Use an update query;

Simply put [product_quantity]-1 in the update part (below the [product_quantity] field. If you want to run it programmatically, just call the update query in VBA.

 

[danb]

Thanks,

I'm not familiar with update queries, and don't really have the time to learn on this project to be honest. Is there a way to do this using ADO?

 

[reclusivemonkey]

There's nothing to "learn"; its exactly the same as a regular query. Keep to the K.I.S.S. principal wherever possible. Its far quicker to create an update query (it will take you seconds) than to do it via ADO. Open a new query, change it to an update query, add the field [product_quantity], then the line I gave you above. Done.

 

[danb]

Okay, I'll give it a go. I need to pass in a value for product_id from the website, and have this query then subtract 1 from the corresponding product_quantity field.

How do I call this update query from my web page?
How do I pass the product_id value into the query?
How do I construct the query to accept a value for product_id?


Thanks for your help on this, much appreciated...

 

[danb]

Hmm, actually, I'm stuck at the first hurdle. I'm using Access 2003 - how do I create an update query?

Is it possible to pass values into the query?

ADO might be simpler for me you know...

 

[Jibbadiah]

Amazing... never seen an update query?!
Go to Access db window.
Go to queries tab.
Create a new select query in design view against the table and field that you want.
Click on the query option of your Access toolbar and select 'update query'.
Add the line that reclusivemonkey has given you... [product_quantity]-1 in the 'update to' section.
BINGO!

 

[danb]

Okay, thanks jibbadiah. How do I call this from a web page and pass in the product_id, so that it only decrements one single record (I don't just want to subtract 1 from all product quantities).

 

[Jibbadiah]

Don't know about the web page stuff... but if you want to just update a given record then put in some criteria in the criteria section of the update query.

 

[danb]

Can anyone help with this, I'm really stuck? I'm sure this can be done quite easily with ADO??

 

[danb]

Actually, this works perfectly:

update products
   set product_quantity = product_quantity - 1
 where product_id = 937

How simple!

 

[Jibbadiah]

if you want to just update a given record then put in some criteria in the criteria section of the update query.

...if you did this and then looked at the query in SQL view... You would have ended up with the same conclusion. It saves you trying to work out the correct syntax, because it does it for you.

 

[neileg]

Nice bit of chat, here, but the design is flawed. You should not hold a balance quantity like this, you should hold a series of caculations and calculate the balance on the fly. Updating the value in the record as you suggest will leave you with no audit trail and potential conflicts if two users try and update at the same time.

You're goin to say that you don't have time to do this that way now and your way is perfectly OK. Believe me, the time spent now will seem as nothing when you're trying to correct the design in the future.