We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Best Possible solution

thenone
thenone asked
on
Medium Priority
148 Views
Last Modified: 2010-08-05
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.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2014

Commented:
Update TableA Inner Join TableB on TableA.Column1 = TableB.Column1
Set Column2 = Column2 + 1

Author

Commented:
The thing is I have a bunch of records that need to be compared with.
CERTIFIED EXPERT
Top Expert 2014

Commented:
<<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)
Software Architect
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
aikmark yes.I think using a combination of a query and stepping through each record and query against b would work.

Author

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)

Author

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?
CERTIFIED EXPERT
Top Expert 2014

Commented:
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.

Author

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.
CERTIFIED EXPERT
Top Expert 2014
Commented:
feed my SQL string into your execute method
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.