Update table using partial word match

[SQL Learner]

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

[othellomy]

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

[Alex Kuznetsov]

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/

[SQL Learner]

Thanks again, Alex.

I am having problem put the SQL in Access. Could you tell me the logic
of the select statement?

(SELECT MIN(Value) FROM TblWord_Number WHERE Street LIKE '%'+Word+'%'
AND Street LIKE CAST(Number AS VARCHAR(50))+'%')

Why are we using Min(Value) and what does "CAST(Number AS
VARCHAR(50))+'%')" mean and do?

I got your partial matching SQL code as follow:

========================
SELECT t.*
FROM tblStreet AS t
WHERE (((Exists (SELECT 1 FROM tblWord w WHERE t.Street LIKE
'*'+Word+'*'))<>False));
========================

I seems a little different from your new one.

This seems like a difficult query. Please help.

- Grasshopper -

[Alex Kuznetsov]

SQL Learner wrote:

Thanks again, Alex.

I am having problem put the SQL in Access. Could you tell me the logic
of the select statement?

(SELECT MIN(Value) FROM TblWord_Number WHERE Street LIKE '%'+Word+'%'
AND Street LIKE CAST(Number AS VARCHAR(50))+'%')

Why are we using Min(Value) and what does "CAST(Number AS
VARCHAR(50))+'%')" mean and do?

I got your partial matching SQL code as follow:

========================
SELECT t.*
FROM tblStreet AS t
WHERE (((Exists (SELECT 1 FROM tblWord w WHERE t.Street LIKE
'*'+Word+'*'))<>False));
========================

I seems a little different from your new one.

This seems like a difficult query. Please help.

- Grasshopper -

If Number column is numeric, I cannot concatenate '%' to it directly, I
need to convert is to character type first.
Because the subquery without MIN() can return more than one value and
blow up, I need to restrict it to one value. i could use TOP 1 instead
of MIN(). You might get better help an an Access newsgroup.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[SQL Learner]

Hi Alan,

Thank you again for your help. I just posted the same question in the
Access group 1 hour ago.

Let's assume in Access (or SQL Server), the text and number can be
concatenated. Could you change your current code for me based on that
assumption?

Also, I thought the old code

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

can be used for the new one?

- Grasshopper -