PDA

View Full Version : SQL statement

Tony McCluney
02-26-04, 12:38
strsql = "UPDATE Shipping SET Shipping.Qty = Qty - 1 " _
& "WHERE (((Shipping.Qty)>0) " _
& "AND ((Shipping.PartNo)= '" & Forms![Axle]![Axle No] & "'"
& "))" _
& ""

dbs.Execute strsql

The above is VB code in an Access2000 application. I need it to update the first record that matches the criteria and no others. It currently updates each record in the Shipping table that matches the Axle No from the form.
Cinil
02-27-04, 09:47
Hi
Your current statement is "Update tblname set cond1='cond1'
Change it to "Update tblname set cond1=(select top1 from tblname where cond1='cond1)".
Let me know if this works.Good luck.
Cinil
Tony McCluney
02-27-04, 11:45
Originally posted by Cinil
Hi
Your current statement is "Update tblname set cond1='cond1'
Change it to "Update tblname set cond1=(select top1 from tblname where cond1='cond1)".
Let me know if this works.Good luck.
Cinil

Cinil,
Thank you for your response. I am still having trouble with the syntax of the SQL statement. I am not very familiar with SQL code and reqire a bit more explicit direction.
Tony
Cinil
02-27-04, 12:26
Hi Tony,
I regret my first reply bcos it doesnt hold true always.The answer to your question is NO unless you change your WHERE clause.Else it will update all the rows.Try to find out some unique ids which you can include in your WHERE clause.The other way,but little more complicated, is to use cursors and exit from the loop after the first row.Having fun with sql...?
Cinil


Originally posted by Tony McCluney
strsql = "UPDATE Shipping SET Shipping.Qty = Qty - 1 " _
& "WHERE (((Shipping.Qty)>0) " _
& "AND ((Shipping.PartNo)= '" & Forms![Axle]![Axle No] & "'"
& "))" _
& ""

dbs.Execute strsql

The above is VB code in an Access2000 application. I need it to update the first record that matches the criteria and no others. It currently updates each record in the Shipping table that matches the Axle No from the form.