SQL syntax [RESOLVED]

just wondering..

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

VB Code:

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..

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

Code:

---

UPDATE Table1 SET Qty = Qty - 1

DELETE FROM Table1 WHERE Qty <= 0

---

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

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.

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?

thx RobDog888 and RhinoBull

Quote:

---

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