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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.