• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

vb: updating records

Hi,

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:

Module:

Public Sub Conn_Open
 strInsert = "select * from tblCustomer"
strPurchase = "select * from tblPurchase"
With conn
.CommandTimeout = 150
.ConnectionString = ""
.ConnectionTimeout = 150
If conn.State = adStateClosed Then
conn.Open
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

Form:

Private Sub Command1_Click()
modconn.Conn_Open
Do While Not rstPurchase.EOF
If rstInsert.EOF=True or rstInsert.BOF=True Then
    rstInsert.MoveFirst
End If
strI_Customers=rstInsert.Fields("Customer").Value
strI_CustNo=rstInsert.Fields("CustNo").Value
strP_CustNo=rstPurchase.Fields("CustNo").Value
  If strI_Customers=rstPurchase.Fields("Customer").Value and strI_CustNo=strP_CustNo Then
      rstInsert.MoveNext
      rstPurchase.MoveNext
 ElseIf strI_CustNo=strP_CustNo And IsNull(rstPurchase.Fields("Customer").Value) Then
      rstPurchase.Fields("Customer").Value=strI_Customers
      rstPurchase.Update
      rstPurchase.MoveNext
      rstInsert.MoveNext
 Else
     rstInsert.MoveNext
 End If
Loop
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.


Thanx
0
a_mihajlo
Asked:
a_mihajlo
  • 3
  • 2
1 Solution
 
jonnietexasCommented:
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.
0
 
coopzzCommented:
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
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_82n9.asp
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)
0
 
coopzzCommented:
how'd you go  .. any luck ??
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
a_mihajloAuthor Commented:
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.... :-))))
0
 
coopzzCommented:
so ya gonna close the question then.

glad to help
0
 
a_mihajloAuthor Commented:
thanx again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now