query to update based on similarity

[subs]

i have two different tables - i want to compare two particular columns
in the two tables . the columns are called as origin ciy in both the
tables. Some of the entires in both of the columns are similar but not
exactly the same. So i want to write a query which can update the
entries in table 1 with the values in table 2- only when the entries
are similar but not exactly the same. for example if the original col
in both the tables look like this-

ocity(table1) ocity(table 2)
santa fe spri santa fe springs
chicago chicago
west sacramen west sacramento
atglen agtlen
missouri missouri

i want a query which can show the result like this

ocity(table1)
santa fe springs
chicago
west sacramento
atglen
missouri

So if you see the above result carefully, table 1 is updated with
table 2 values only when entries are similar but not exactly the
same . So may be the query has to compare the first six characters of
the table 1 with that of table 2, then if they are same, it should be
updated with that of table 2. can you pls help . Thanks

 

[Marshall Barton]

i have two different tables - i want to compare two particular columns
in the two tables . the columns are called as origin ciy in both the
tables. Some of the entires in both of the columns are similar but not
exactly the same. So i want to write a query which can update the
entries in table 1 with the values in table 2- only when the entries
are similar but not exactly the same. for example if the original col
in both the tables look like this-

ocity(table1) ocity(table 2)
santa fe spri santa fe springs
chicago chicago
west sacramen west sacramento
atglen agtlen
missouri missouri

i want a query which can show the result like this

ocity(table1)
santa fe springs
chicago
west sacramento
atglen
missouri

So if you see the above result carefully, table 1 is updated with
table 2 values only when entries are similar but not exactly the
same . So may be the query has to compare the first six characters of
the table 1 with that of table 2, then if they are same, it should be
updated with that of table 2.

It's easy enough to check the first 6 characters:

Where Left(Table1.ocity,6) = Left(Table2.ocity,6)
And Len(Table1.ocity,6) < Len(Table2.ocity,6)

Or, it might(?) be better to check if the table1 value is a
truncated copy of the table2 value:

Where Table2.ocity Like Table1.ocity & "*"
And Len(Table1.ocity,6) < Len(Table2.ocity,6)

OTOH, checking for situations like your
atglen agtlen
mismatch is a completely different ball game that in general
can not be done accurately.