Posted by SQL Learner on 01/20/07 17:33

Hi Alex (Kuznetsov) and All,

This is to follow up with my last post, "Link two tables using partial
word match".
How can I UPDATE table using partial word match?
How can I write a SQL statement to do so? (I am using MS-Access.)
The two tables and the expected result are listed below:

Thank you in advance!

- Grasshopper -

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

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


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

[Back to original message]