how do i use select statement in update query

[avinash]

hi myself avi
i am developing one appliacaion in which i am using vb 6 as front end,
adodb as database library and sql sever 7 as backend.
i want to update one table for which i required data from other table. and
iretrive data from second table by giving some condition. when i get data,
then to update first table i need to use do while loop. instead of that i
want to use select statement directly in update query.
plz give me some help.
following is the my queries and its out put
StrSql = ""
StrSql = "Select * From SalesVchMaterialDesc where TransactionID=" &
txtTransactionID.text & ""
rsMName.Open StrSql, Conn, adOpenKeyset
Do While Not rsMName.EOF
StrSql = ""
StrSql = "Update StockTable Set Outward=Outward - " &
rsMName("Netweight") & ",OutwardQty=OutwardQty - " & rsMName("Qty") & "
Where MaterialId=" & rsMName("Material_Name") & " and VoucherDate='" &
Format(rsMName("VoucherDate"), "mm/dd/yyyy") & "'
RsAdd.Open StrSql, Conn, adOpenStatic
rsMName.MoveNext
Loop
rsMName.Close
out put
***main query
Select * From SalesVchMaterialDesc where TransactionID=848
do while not loop
Update StockTable Set Outward=Outward - 8.06,OutwardQty=OutwardQty - 1
Where MaterialId=221 and VoucherDate='04/01/2004' and SMID=0
loop

[Simon Hayes]

"avinash" <pa***********@rediffmail.com> wrote in message
news:cb******************************@localhost.ta lkaboutdatabases.com...

hi myself avi
i am developing one appliacaion in which i am using vb 6 as front end,
adodb as database library and sql sever 7 as backend.
i want to update one table for which i required data from other table. and
iretrive data from second table by giving some condition. when i get data,
then to update first table i need to use do while loop. instead of that i
want to use select statement directly in update query.
plz give me some help.
following is the my queries and its out put
StrSql = ""
StrSql = "Select * From SalesVchMaterialDesc where TransactionID=" &
txtTransactionID.text & ""
rsMName.Open StrSql, Conn, adOpenKeyset
Do While Not rsMName.EOF
StrSql = ""
StrSql = "Update StockTable Set Outward=Outward - " &
rsMName("Netweight") & ",OutwardQty=OutwardQty - " & rsMName("Qty") & "
Where MaterialId=" & rsMName("Material_Name") & " and VoucherDate='" &
Format(rsMName("VoucherDate"), "mm/dd/yyyy") & "'
RsAdd.Open StrSql, Conn, adOpenStatic
rsMName.MoveNext
Loop
rsMName.Close
out put
***main query
Select * From SalesVchMaterialDesc where TransactionID=848
do while not loop
Update StockTable Set Outward=Outward - 8.06,OutwardQty=OutwardQty - 1
Where MaterialId=221 and VoucherDate='04/01/2004' and SMID=0
loop

Here is one possibility, using MSSQL proprietary UPDATE syntax:

update
stocktable
set
outward = outward - sm.netweight,
OutwardQty=OutwardQty - sm.qty
from
stocktable st
join SalesVchMaterialDesc sm
on st.TransactionID = sm.TransactionID
where
sm.TransactionID = 848
and st.materialid = sm.materialname
and st.voucherdate = sm.voucherdate

Note that this is a guess (and probably wrong), because you did not provide
CREATE TABLE and INSERT statements for your tables, so we have to guess what
your data types, keys and constraints are, and what your data looks like:

http://www.aspfaq.com/etiquette.asp?id=5006

Also, your "output" mentions a column called SMID, which isn't in the VB
code. But in any case, you should try to write a TSQL set-based UPDATE
statement for operations like this - it will be much faster than looping
over a result set and building multiple dynamic statements, especially if
you need to UPDATE multiple rows at once. The best practice here would
probably be to put your query in a stored procedure which accepts
@TransactionID as a parameter, then use an ADO Command object to execute it.

If you're not comfortable with TSQL syntax, you might want to get hold of a
basic book such as Sam's "Teach Yourself Transact SQL in 21 Days" - it will
save you a lot of time in the long run.

Simon