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

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.
0
thenone
Asked:
thenone
  • 5
  • 4
2 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now