updateing city name at table1 from table1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

updateing city name at table1 from table1

Author

Topic

pssheba
Yak Posting Veteran

95 Posts

Posted - 2007-10-28 : 12:32:37

Hi everyone,

I have 2 tables: t1 and t2. Each table contains a "city" column. In t1 some city names have additional remark closed between parentheses eg

(mycity (wonderful)) and i want city names in t2 to have those parentheses added. I do it as follows:


UPDATE b
SET
b.city=a.city
FROM
t1 AS a LEFT JOIN t2 AS b 
ON
SUBSTRING(a.city,1,CHARINDEX('(',a.city)-1)=b.city
WHERE
a.city LIKE '%(%'

Running the above code i got NULL to all city names in t2 !

At the othe hand when i run:


SELECT
a.city,
b.city
FROM
t1 AS a LEFT JOIN t2 AS b
ON
SUBSTRING(a.city,1,CHARINDEX('(',a.city)-1)=b.city
WHERE
a.city LIKE '%(%'

I get the "parenthesesed" cities side by side with the "non parenthesesed" cities.

Can anyone solv that mystery ?

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-28 : 12:53:42

In the first Statement

You are specifying to Set b.City to be potentially null, because you are doing a LEFT join From A to B meaning you will get ALL records from A, and only matching records from B (and then Setting to the "matched" record). That all results set to NULL means the JOIN is not working the way you wanted.

Interestingly, you are doing an update to a column that the join itself depends on...I am not sure that is your intent.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-28 : 12:55:32

Ooops. I re-read that and it seems you want the city in B to have the parenthesis..

Update T2
Set City = '(' + RTRIM(City) + ')'
Where Left(City,1) not in ('(')

Edited once again...Set b.City = a.City From a inner join b on (Some other column) to get the full value of a.City into b.City

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 05:58:41

Why are you joining T1 at all? Don't you just want to remove the (xxx) stuff from CITY column in T2?

Kristen

pssheba
Yak Posting Veteran

95 Posts

Posted - 2007-10-29 : 07:36:24

quote:
Originally posted by dataguru1971

Ooops. I re-read that and it seems you want the city in B to have the parenthesis..

Update T2
Set City = '(' + RTRIM(City) + ')'
Where Left(City,1) not in ('(')

Edited once again...Set b.City = a.City From a inner join b on (Some other column) to get the full value of a.City into b.City

Thanks a lot dataguru, it worked fine !

pssheba
Yak Posting Veteran

95 Posts

Posted - 2007-10-29 : 07:38:25

quote:
Originally posted by Kristen

Why are you joining T1 at all? Don't you just want to remove the (xxx) stuff from CITY column in T2?

Kristen

Hi Kristen,
On the contrary, i wanted to add the (xxx) to t2 according to t1.

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 08:34:28

OK, I think I get it now!

T1.CITY has "London (grey)"
T2.CITY has "London"

You would like to update T2.CITY to become "London (grey)"

I don't see how dataguru's query does that, but I have probably misunderstood something

If I have understood I highly recommend that you split the data in T1 into two columns - "City" and "Comment"

Kristen

pssheba
Yak Posting Veteran

95 Posts

Posted - 2007-10-29 : 10:35:41

quote:
Originally posted by Kristen

OK, I think I get it now!

T1.CITY has "London (grey)"
T2.CITY has "London"

You would like to update T2.CITY to become "London (grey)"

I don't see how dataguru's query does that, but I have probably misunderstood something

If I have understood I highly recommend that you split the data in T1 into two columns - "City" and "Comment"

Kristen

Hi kirsten,
The thing is: That city list came at first without region added. What you suggest is what i did at first, i assigned regions ("west", south" etc..) to a "region" column but that didnt work properly because once the user selected "London", "region" column was not assigned anything. I had to run a query that decided which is the right region. We use a package which code i have no access. The only control i can get is via database. So, if the user selects, "London (west)" than i can classify clients on a region order.
Hopefully this explanation is more clear and thank you for trying to give a hand.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-29 : 10:39:45

I think it will be clearer for everybody if you can post your table DDL, sample data and the expected result

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources