SQL syntax [RESOLVED]

[erickwidya]

just wondering..

if i open table via recodset i can do something like this..

rs("qty") = rs("qty") - 1
if rs("qty") <= 0 then rs.delete

then what i ask is if i want to use UPDATE SQL..is there possible to do something like that?

thx for the input..

[RobDog888]

No need for a recordset. I think these will do what you asked. Execute on "TEST" table first.

UPDATE Table1 SET Qty = Qty - 1

DELETE FROM Table1 WHERE Qty <= 0

[erickwidya]

thx RobDog888 for the reply
try that at the query and it give me an error, maybe can't use more than one SQL statement at query?

PS :i forgot to tell u that i work with MS Access and want the syntax in one SQL statement

[RhinoBull]

You can't execute two statements at once regardless of db type. You may, however, use transaction and if one of statements fails - Rollback everything that was done so your data will remain as it was prior to BeginTrans.

[RobDog888]

To elaborate on what RB posted.... You definately can't execute two sql statements
that perform a transaction and one is dependent on the other.

Why cant this be in a batch or two executions?

[erickwidya]

thx RobDog888 and RhinoBull

Why cant this be in a batch or two executions?

yes it can done via two executions, i'm just wonder if it can done just for one

well guess u guys have answered my question..
thx again