|
Posted by Alex Kuznetsov on 01/22/07 15:19
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
Try something like this (untested, I don't have Access):
UPDATE tblStreet_Value SET VALUE2 =
(SELECT MIN(Value) FROM TblWord_Number WHERE Street LIKE '%'+Word+'%'
AND Street LIKE CAST(Number AS VARCHAR(50))+'%')
-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
Navigation:
[Reply to this message]
|