Update column from another table

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

General SQL Server Forums

New to SQL Server Programming

Update column from another table

Author

Topic

asiddle
Starting Member

35 Posts

Posted - 2007-04-26 : 10:29:05

Hello all, was wondering if you could point me in the right direction for this.

I have a db with a column classifications, and in that column are 'Accountants - (1234)' 'Book-keepers - (18) etc etc. Now what I want to do is remove the - (xxxx) section and obviously the white space, so I am just left with 'Accountants' 'Book-keepers' etc.

1. Is there an easy way to do this?

Ok so my thoughts were create a another table and put the ammended classifications in that to update the first table. Your probably asking why not just do it on the first table...Answer. There are over 150,000 records to change.

So I now have my first table with column classification and my second table with the correct classification ammendments.

I can sit down and manually type

UPDATE table1
SET classifications = 'Accountants'
Where classifications = 'Accounts - (xxxx)'

until i have completed the entire list in table 2 but I was hoping you good people would know a way to work through the list so it would automatically update each classification correctly.

The problem I have here is I dont know how to work through table 2 and match it to something in table 1 and update it.

Any help is greatfully appreciated

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 10:34:28

quote:
I have a db with a column classifications, and in that column are 'Accountants - (1234)' 'Book-keepers - (18) etc etc. Now what I want to do is remove the - (xxxx) section and obviously the white space, so I am just left with 'Accountants' 'Book-keepers' etc.

Is it what you are trying to remove is of standard format ? dash - space - left parenthesis - some chars - right parenthesis ?

you can try this


update t
set    classification = rtrim(left(classification, charindex('- (', classification) - 1))
from   table1 t

Or the mapping table approach


update t
set    classification = m.new_classification
from   table1 t inner join table2 m
on     t.classification = m.old_classification

asiddle
Starting Member

35 Posts

Posted - 2007-04-26 : 11:19:00

May thanks for your quick reply. I have tried your first approach but keep getting errors in the query analyzer

And I know I am typing it wrong.

Db Name: Listingdb
Table Name: CompanyDetails
Column Name: Classification

UPDATE Listingdb
SET Classification = rtrim(left(classification, charindex('- (', classification) - 1))
FROM Listingdb

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 11:24:08

Shouldn't you be updating the table rather than the database ?


UPDATE Listingdb c
SET   Classification = rtrim(left(classification, charindex('- (', classification) - 1))
FROM Listingdb CompanyDetails c

asiddle
Starting Member

35 Posts

Posted - 2007-04-26 : 11:31:53

Yes sorry that was a typo. Still have error though.

UPDATE c
SET Classification = rtrim(left(classification, charindex('- (', classification) - 1))
FROM CompanyDetails c

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 11:37:22

oh you have some classification that does not contain the '- (xxxx)'


UPDATE c
SET Classification = case when charindex('- (', classification) <> 0 
                          then rtrim(left(classification, charindex('- (', classification) - 1))
                          else classification
                          end
FROM CompanyDetails c

Do note that this method assumes the pattern that you want to remove is '- (xxxx)'

asiddle
Starting Member

35 Posts

Posted - 2007-04-26 : 11:43:17

Ok, well here is some sample data

PLASTERERS, SCREEDERS & DRY LINING CONTRACTORS - (4039)
LIGHTING CONSULTANTS - (277)
SOFT FURNISHINGS - RETAIL - (1370)
CAR IMPORTS - (78)
RESTAURANTS - OTHER - (4989)
TAKE AWAY FOOD SHOPS - (18213)

Its the last ' - ()' bit that I need to remove, but have to be carefull of things like RESTAURANTS - OTHER

asiddle
Starting Member

35 Posts

Posted - 2007-04-26 : 11:46:04

I did try in excel remove all 1s 2s 3s 4s 5s 6s 7s 8s 9s 0s ( ) and -

But then found myself going through and adding all the - back to RESTAURANT OTHER etc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 11:46:46

[code]
select classification,
new_classification =
case when charindex('- (', classification) <> 0
then rtrim(left(classification, charindex('- (', classification) - 1))
else classification
end
from (
select classification = 'PLASTERERS, SCREEDERS & DRY LINING CONTRACTORS - (4039)' union all
select 'LIGHTING CONSULTANTS - (277)' union all
select 'SOFT FURNISHINGS - RETAIL - (1370)' union all
select 'CAR IMPORTS - (78)' union all
select 'RESTAURANTS - OTHER - (4989)' union all
select 'TAKE AWAY FOOD SHOPS - (18213)' union all
select 'OTHERS'
) a
/*
classification new_classification
------------------------------------------------------- -------------------------------------------------------
PLASTERERS, SCREEDERS & DRY LINING CONTRACTORS - (4039) PLASTERERS, SCREEDERS & DRY LINING CONTRACTORS
LIGHTING CONSULTANTS - (277) LIGHTING CONSULTANTS
SOFT FURNISHINGS - RETAIL - (1370) SOFT FURNISHINGS - RETAIL
CAR IMPORTS - (78) CAR IMPORTS
RESTAURANTS - OTHER - (4989) RESTAURANTS - OTHER
TAKE AWAY FOOD SHOPS - (18213) TAKE AWAY FOOD SHOPS
OTHERS OTHERS
*/
[/code]

asiddle
Starting Member

35 Posts

Posted - 2007-04-26 : 11:50:33

Going down the mapping route how do I do that?

I have the following

DB Name: Classifications
Table Name: AZClassifications
Column: Classification

This is the table with all the -(xxx) removed
e.g.
Accountants
Abattoirs
Advertising - Media

Then the other table I wastalking about you already have details for

asiddle
Starting Member

35 Posts

Posted - 2007-04-26 : 11:51:43

Ok so with the sample you just made I have to type out all 3000 classifications right?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-26 : 11:58:04

or rather than filtering it at CASE level, restrict rows in WHERE clause.

Select	classification, 
	new_classification = rtrim(left(classification, charindex('- (', classification) - 1))
from   	(
		select 	classification = 'PLASTERERS, SCREEDERS & DRY LINING CONTRACTORS - (4039)'	union all
		select	'LIGHTING CONSULTANTS - (277)'	union all
		select	'SOFT FURNISHINGS - RETAIL - (1370)'	union all
		select	'CAR IMPORTS - (78)'	union all
		select	'RESTAURANTS - OTHER - (4989)'	union all
		select	'TAKE AWAY FOOD SHOPS - (18213)'	union all
		select	'OTHERS'
	) a
where
	charindex('- (', classification) <> 0

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

asiddle
Starting Member

35 Posts

Posted - 2007-04-26 : 12:03:18

Ok guys, but are you saying I need to type out all 3000 categories in the list?

If so then surely I may as well stick with what I was doing originally

UPDATE CompanyDetails
SET Classification = 'CAR IMPORTS'
WHERE Classification = 'CAR IMPORTS - (78)'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-26 : 12:09:05

No...it's just an example to show you the concept. You can use same technique in your update query.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

asiddle
Starting Member

35 Posts

Posted - 2007-04-26 : 12:11:30

Ok so if thats just an example what is it I have to enter to change all the categories?

Thanks to both of you so far though for your assistance

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-26 : 12:18:06

[code]UPDATE c
SET Classification = rtrim(left(classification, charindex('- (', classification) - 1))
FROM CompanyDetails c
Where
charindex('- (', classification) <> 0[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

asiddle
Starting Member

35 Posts

Posted - 2007-04-26 : 12:32:05

whilst I havent got a clue what i just done....it worked a treat. A BIG THANKYOU to you both

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources