| Author |
 Topic  |
|
|
bjlt
Senior Member
1144 Posts |
 Posted - 16 October 2003 : 06:58:16
|
possible to do it in one query?
rs = select field from table
update field = rs(field)+1
and one advantage of stored procedure is that sp can do tasks like the one above in one db call, right?
Thanks in advance. |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 16 October 2003 : 07:28:57
|
uhm, what exactly are you trying to do, your example does not make much sense
|
 |
|
|
bjlt
Senior Member
1144 Posts |
Posted - 16 October 2003 : 07:35:48
|
f1 : type int
ssql = "select f1 from t1 where ..."
set rs = ...
iF1 = rs("f1")
....
ssql = "update t1 set f1 = (iF1 + 1) ..."
basically, I want to update a field based on its original value, e.g. +1 or -1, and wondering if it's possible to do it in one query in asp.
I hope this time I've made myself clear 
|
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 16 October 2003 : 07:41:26
|
you can do that with a simple sql call,
update table set field1 = field1+1 where..... |
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
|
|
bjlt
Senior Member
1144 Posts |
Posted - 16 October 2003 : 07:46:40
|
Thanks HuwR and Nikkol. 
Is it possible to make sure the value is 0+ in that query? I mean, if field1 < 0 then field1 = 0?
Thanks. |
Edited by - bjlt on 16 October 2003 07:48:11 |
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 16 October 2003 : 08:57:22
|
update t1
set f1 = case when f1<0 then 0 else f1+1 end
where ... |
|
|
|
bjlt
Senior Member
1144 Posts |
Posted - 16 October 2003 : 10:41:39
|
quote:
update t1
set f1 = case when f1<0 then 0 else f1+1 end
where ...
Thanks. I got a missing operator error. does it work in asp with Access as the db?
"UPDATE TABLE " &_
"SET F1 = (CASE WHEN F1 < 0 THEN 0 ELSE (F1 - 1) END)"... |
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 16 October 2003 : 12:33:47
|
quote:
Originally posted by bjlt
I got a missing operator error. does it work in asp with Access as the db?
It will work in MS SQL Server, Sybase ASE and Oracle (depending on version).
I'm not sure about access. |
Edited by - pweighill on 16 October 2003 12:34:16 |
|
|
|
bjlt
Senior Member
1144 Posts |
Posted - 16 October 2003 : 12:37:57
|
I tested as an sql statement in asp on Access and got the error, guess it won't work with access. I may creat a sp for access and see if it works that way.
Thanks. |
|
|
|
bjlt
Senior Member
1144 Posts |
Posted - 16 October 2003 : 12:59:48
|
| well, it seems I can't creat a sp for access with those statement. |
|
|
| |
Topic |
|
Topic Locked
