Update records based on instr

[Guest]

LS,

I am trying to do the following. I have two tables in which I store date.
The first table contains a column named "Description" and "Connector". The
second table contains a column named "Text".
For example:
Table1:
Description
This is a test
This is working
I am confused

Table2:
Text
Test
Working
Confused

Now I am trying to update table1 "Connector" column with the data stored in
table2. This should be done when the text in table2 is found in the
"description" column of table 1.
The result should be
Table1:
Description Connector
This is a test Test
This is working Working
I am confused Confused

I have tried to do this with a update query and the instr function, but I
can not figure it out. Can anybody please help

tia

 

[Tom Ellison]

Dear Lee:

I recommend you use this query to test my proposal. Since the information
in Table1 seems to be free-typed, things can get pretty funny. What if
someone put "This is a working test" in one row? Which way to you want it
updated?

SELECT T1.Description, T2.Text
FROM Table1 T1, Table2 T2
WHERE T1.Description LIKE "*" & T2.Text

This matches on the end of the Description. If you want to match rows on
"containing" the string, add:

& "*"

to the end of the query above.

For purists, I deliberately did not use a non-equijoin construction. When
things get messy, I prefer the cross-product construction. It's hard to
explain exactly why. I associate JOIN with deliberately designed,
intentionally constrained relationships with indexes. The above is an
ambiguous set relationship that may approach many-to-many.

Tom Ellison