vb: updating records

Posted on 2004-09-06
Last Modified: 2010-04-03

I've got two tables with about 18000 records. I need to grab a value from TableA and push it into TableB (provided that TableA.ColumnB=TableB.ColumnB). This is what I've got so far:


Public Sub Conn_Open
 strInsert = "select * from tblCustomer"
strPurchase = "select * from tblPurchase"
With conn
.CommandTimeout = 150
.ConnectionString = ""
.ConnectionTimeout = 150
If conn.State = adStateClosed Then
End If
End With

With rstInsert
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
If rstInsert.State = adStateClosed Then
rstInsert.Open strInsert, conn
End If
End With

With rstPurchase
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
If rstPurchase.State = adStateClosed Then
rstPurchase.Open strPurchase, conn
End If
End With

End Sub


Private Sub Command1_Click()
Do While Not rstPurchase.EOF
If rstInsert.EOF=True or rstInsert.BOF=True Then
End If
  If strI_Customers=rstPurchase.Fields("Customer").Value and strI_CustNo=strP_CustNo Then
 ElseIf strI_CustNo=strP_CustNo And IsNull(rstPurchase.Fields("Customer").Value) Then
 End If
end sub

Now the code works and it does it's job, however it takes ages to update the Purchase table as it goes row by row.

Have you got any idea as to how to achieve the same in a faster way, as believe me it takes about 7h to update 18000records.

Any advice much appreciated.

Question by:a_mihajlo
  • 3
  • 2

Expert Comment

ID: 11993481
Icky.  Managing data integrity through code blows. Use SQL to do it.  Now, I'm an oracle jockey so it might be a bit different with syntax but hopefully you'll get the idea.

update tableb b1 set b1.value =  (select a.value from tablea a, tableb b
where a.ColumnB=b.ColumnB
and b.key = b1.key)

The difference it that you're doing a full table scan of the 18000 rows every time you look at a row so really 18000*18000 and that's a lot.
LVL 11

Accepted Solution

coopzz earned 500 total points
ID: 12004169
I believe this may be what your after

before you try Clarify: this query should update tblPurchase.Customer column when CustNo are equal and Customer are not equal

UPDATE tblPurchase
      SET tblPurchase.Customer = tblCustomer.Customer
      FROM tblPurchase, tblCustomer
      WHERE tblPurchase.CustNo = tblCustomer.CustNo AND tblPurchase.Customer <> tblCustomer.Customer

I haven't tested it but the Syntax checks out in a query analyser  as with all big updating querys ##NOTE: USE WITH CAUTION.. Make sure you have backups.

the idea is taken from T-sql reference below
the example.
C. Use the UPDATE statement using information from another table
This example modifies the ytd_sales column in the titles table to reflect the most recent sales recorded in the sales table.

UPDATE titles
   SET ytd_sales = titles.ytd_sales + sales.qty
      FROM titles, sales
         WHERE titles.title_id = sales.title_id
         AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
LVL 11

Expert Comment

ID: 12098231
how'd you go  .. any luck ??
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


Author Comment

ID: 12198816
I fixed it with this sql string:

strSql="update TableB set TableB.ColumnA=TableA.ColumnA from TableA, TableB where TableA.ColumnB=TableB.ColumnB"

And now it works like magic!!!!

Thanx a lot for all the valuable comments you guys have posted.... :-))))
LVL 11

Expert Comment

ID: 12199282
so ya gonna close the question then.

glad to help

Author Comment

ID: 12199328
thanx again

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all have limited time to study long and complicated information about RAID theories, but you may be interested as to how RAID 5 works. We made it simple for you by providing the shortest and easiest explanation ever.   First we need to remind …
In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question