Posted by othellomy on 01/22/07 09:46

Hi,
This might work...
update tblStreet_Value
set Value2 = TblWord_Number.Value2
from TblWord_Number
where patindex(TblWord_Number.Number+ ' ' + TblWord_Number.Word +
'%',Street) > 0


SQL Learner wrote:
> 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]