473,394 Members | 1,507 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

how do i use select statement in update query

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

Jul 20 '05 #1
1 11097

"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
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
4
by: 001 | last post by:
Hello, The select statement needs only 1 second to complete the query. But the update statement spends 30 minutes. Why? SELECT STATEMENT: declare @IDate smalldatetime select @IDate=col001...
4
by: Polly | last post by:
I had a macro that ran a parameter query and created and opened an Excel file with the system date as part of the file name, but I had to change the file name by hand. So I converted the macro to...
6
by: GSteven | last post by:
(as formerly posted to microsoft.public.access.forms with no result) I've created a continuous form which is based on a straightforward table (ex - customers - 100 records). On the form there is...
4
by: Nick Barr | last post by:
Hi, I am trying to gather stats about how many times a resource in our web app is viewed, i.e. just a COUNT. There are potentially millions of resources within the system. I thought of two...
9
by: P3Eddie | last post by:
Hello all! I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same. My problem may be a simple find duplicates / do something...
2
by: DuncanIdaho | last post by:
Hi Apologies if this is similar to a (very) recent post. I was wondering if it is possible to execute an update query that contains a select statement on an MS access 2000 database. I have...
1
by: themightyrhino | last post by:
I'm writing a process to perform multiple updates on a main table from information stored in different component reports. As you can see from the below, I'm using a select query to base the update...
4
by: hapnendad | last post by:
In the question statement below Field names are in and variables are in (). All fields referenced are in what I have named the ‘PAR’ Table. Using MS Access 2003, I am working on a project...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.