increment by one 1

[theotrain]

this should be easy, but i dont know how...

can i increment a number in a database by one without first doing a query to get the value out.

im using access and coldfusion.

thanks!

 

[Ed2020]

It depends.

What value? Where is it stored? In a table? What is the number used for? Is it a primary key? Used in relationships with other tables?

Ed Metcalfe.

Please do not feed the trolls.....

 

[theotrain]

its in a table, not a primary key.

maybe i can describe the idea of what im doing overall... for all i know its a bad idea but my database design experience aint much.

lets say i have one table with a crapload of data, and i need to create statistics that will be viewed and updated fairly often. the problem is creating the statistics i need would require a fairly wasteful computation each time, and they will have to be generated often. so i want to have a seperate table that just contains a foreign key and the totals i need to have for each of those items ready-made. so when a new entry is made i can just increment the appropriate record by one and have my statistics ready to go.

so its just a table with a foreign key and a number that i will need to increment using sql and coldfusion.

the only way i would know how to do this is query the record first, then increment that number in CF and update the record... but im sure thats not how its done.

thanks.

 

[PHV]

Why not simply using an AutoNumber field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[theotrain]

the idea was to have a table with the following:

a column of foreign keys
and several columns of statistics applicable to that key.

one of the "other columns" will contain numbers i will need to increment by one, one will be dollar amounts, one will be still other foreign keys. its just a bunch of stuff i think it will be easier to store than to calculate each time they are needed.

i dont know how to use auto increment unless im adding a new record to the database (then auto increment goes up for the new record). im not adding records, merely updating them.

but i guess my question is more general anyway. instead of +1 what if i need to do +2 or +$1.25 ?? is there a way that stuff is done other than one "fetch the data" query and another "update" query? all the sql ive seen is so startlingly efficient and spare i cant believe you would need 2 queries. do you?

 

[PHV]

A starting point (SQL code):
UPDATE yourTable
SET yourCounter=yourCounter+2, yourCurrencyField=yourCurrencyField+1.25
WHERE yourCriteria=True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[theotrain]

as dumb as it sounds, thats all i was looking for!

i didnt know you could just increment like that right in your update statement. fantastic.