Best Possible solution

What I am trying to do is compare table columns.I have a record from table column1 A and what I want to do is try to find it in table B column1.If its found then Table Column2 which is an integer value will be plus 1.
LVL 8
thenoneAsked:
Who is Participating?
 
Alexandre SimõesManager / Technology SpecialistCommented:
Comparing a column is like having 2 tables with 1000 rows and find out if there's a column on table 2 that have a column with the exact same 1000 cells as one colum on table 1 ?

As you can imagine, depending on the amount of rows and columns, this may "kill" your machine for a while. :)

Here is the code...
I tried not to use any .net 2.0 language improvements as I don't know what version you are using.

    Public Function ColumnCompare(ByVal column1 As DataColumn, ByVal column2 As DataColumn) As Int16

        If column1 Is Nothing AndAlso column2 Is Nothing Then Return 1
        If column1 Is Nothing OrElse column2 Is Nothing Then Return -1
        If column1.Table.Rows.Count <> column2.Table.Rows.Count Then Return -1
        If Not column1.DataType Is column2.DataType Then Return -1

        Dim i As Integer
        For i = 0 To column1.Table.Rows.Count - 1
            If column1.Table.Rows(i)(column1) <> column2.Table.Rows(i)(column2) Then Return -1
        Next

    End Function

Alex :p
0
 
aikimarkCommented:
Update TableA Inner Join TableB on TableA.Column1 = TableB.Column1
Set Column2 = Column2 + 1
0
 
thenoneAuthor Commented:
The thing is I have a bunch of records that need to be compared with.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
aikimarkCommented:
<<The thing is I have a bunch of records that need to be compared with.>>
So if it exists anywhere in column1 of tableB, you want to increment it?

Update TableA
Set Column2 = Column2 + 1
Where Exists (Select * From TableB Where TableA.Column1 In TableB.Column1)
0
 
thenoneAuthor Commented:
aikmark yes.I think using a combination of a query and stepping through each record and query against b would work.
0
 
thenoneAuthor Commented:
So basically I could have a for loop or soemthing and then execute the query correct?

Update TableA
Set Column2 = Column2 + 1
Where Exists (Select * From TableB Where TableA.Column1 In TableB.Column1)
0
 
thenoneAuthor Commented:
Alexcode i am using vb6 and your right using that function would slow everyhting down.How can I do it by using a query against one record at a time?
0
 
aikimarkCommented:
No looping required.  Let the database engine do all the work for you.  You will have adequate performance if there are indexes on the two columns.
0
 
thenoneAuthor Commented:
aikmark how would I impliment that into a command.execute

my database is mysql 5.0

Also I see what your saying it will compare one table column with the other table column with one query much faster.
0
 
aikimarkCommented:
feed my SQL string into your execute method
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.

All Courses

From novice to tech pro — start learning today.