How do I Update multiple values in one column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

How do I Update multiple values in one column?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-07 : 11:24:03

David writes "May be a simple thing for you to noodle but I'm stuck.

I have a table with a field named "Customer Name". Now, "Customer Name" needs to become "Customer ID" and to do this I know each customer could be updated, such as: UPDATE tblRequests SET CustomerName = "102" WHERE CustomerName = "Acme Widgets"

However I have several hundred customers to update. How would you approach this task?"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-07 : 11:34:45

you'd have to have as many updates as there are different conditions.

but you'd better give us more details.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-07 : 11:43:46

You could populate a lookup table, such as

create table lookup (oldname varchar(100), newname varchar(100))

and then fill it with all "conversions"

then use a single update as

update mycustomertable
set customername = newname
from mycustomertable, lookup
where customername = oldname

and then use
alter table mycustomertable ...

to change datatype from varchar to int

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-10 : 07:17:59

Well this may look very primitive but it does work.

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Custtbl')
DROP table Custtbl

create table Custtbl(Customer_Names varchar(100),Customer_IDs varchar(50))

insert into Custtbl
select 'Acme Widgets','102' union all
select 'Acme Creams','101'

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'temp1')
DROP table temp1
create table #temp1(Customer_Names varchar(50),Customer_IDs varchar(50))
insert into #temp1(Customer_Names,Customer_IDs)select Customer_IDs,Customer_Names from Custtbl

truncate table Custtbl

insert into Custtbl(Customer_Names,Customer_IDs)select Customer_Names,Customer_IDs from #temp1

drop table #temp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-10 : 10:12:36

quote:
Originally posted by AskSQLTeam

David writes "May be a simple thing for you to noodle but I'm stuck.

I have a table with a field named "Customer Name". Now, "Customer Name" needs to become "Customer ID" and to do this I know each customer could be updated, such as: UPDATE tblRequests SET CustomerName = "102" WHERE CustomerName = "Acme Widgets"

However I have several hundred customers to update. How would you approach this task?"

Do you need to auto assign an ID to the table ?

why not just add a new column to the table ?

alter table tblRequests add CustomerID int identity(1,1)

KH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources