Update table using partial word match

[SQL Learner]

Hi All,

How can I UPDATE table using partial word match?
How can I write a SQL statement in ACCESS to do so? (I am using
MS-Access 2003.)
The two tables and the expected result are listed below:

Thank you in advance!

- Grasshopper -


=======================================================
TABLE: tblStreet_Value (Table to be updated)


Street Value2
--------------- ------
123 ABC Street
124 ABC Street
125 ABC Street
1 XYZ AVE
2 XYZ AVE
3 XYZ AVE
10 CBS Street
11 CBS Street
12 CBS Street
100 Apple Road
101 Apple Road
102 Apple Road


TABLE: TblWord_Number


Word Number Value
---- ------ -----
ABC 123 NY
ABC 125 CA
CBS 11 MA
CBS 12 TX


Expected Result


Street Value2
--------------- ------
123 ABC Street NY
124 ABC Street
125 ABC Street CA
1 XYZ AVE
2 XYZ AVE
3 XYZ AVE
10 CBS Street
11 CBS Street MA
12 CBS Street TX
100 Apple Road
101 Apple Road
102 Apple Road


====================
A side note:
Alex Kuznetsov has provided me a partial match solution for SQL Server:

SELECT t.* FROM tblStreet t WHERE EXISTS(SELECT 1 FROM tblWord w WHERE
t.Street LIKE '%'+Word+'%')

 

[SQL Learner]

Thank you Luca, but that is not what I want.

I want to have a partial word match based on the value on the Word and
Number fields in tblWord_Number and update the Value2 field in
tblStreet_Value with Value.

- GrassHopper -

 

[John Spencer]

Try the following non-equi join query on a COPY of your data.

UPDATE tblStreet_Value as S INNER JOIN tblWord_Number as W
ON S.Street Like W.Number & "*" & W.Word & "*"
SET S.Value2 = [W].[Value]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[SQL Learner]

Hi John,

Thank you so much on your code. It works very well.

I put another '*" before the W.Number as followed

UPDATE tblStreet_Value AS S INNER JOIN tblWord_Number AS W ON S.Street
Like "*" & W.Number & "*" & W.Word & "*" SET S.Value2 = W.Value;

to account for the following Street strings:

East 10 CBS Street
11 CBS Street, Suite 3C
South 12 CBS Street, 6D

Thanks again!

- Grasshopper -