asked on

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.

aikimark

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

thenone

ASKER

The thing is I have a bunch of records that need to be compared with.

aikimark

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

ASKER CERTIFIED SOLUTION

Alexandre Simões

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thenone

ASKER

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

thenone

ASKER

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)

thenone

ASKER

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?

aikimark

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.

thenone

ASKER

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.

SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial