Reply to Re: Update table using partial word match

Your name:

Reply:


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/

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация