update statement - SQL Server Forums

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)

update statement

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-04 : 01:17:02

I have a product table look like:

CategoryID(PK)  Categoryname 
------------------------------
1000              computer
1001              book
1003              music
:                  :
                   :
:                  :
:                  :
        
1500               Movie

and I am trying to update categoryname looks like:

CategoryID(PK)  Categoryname 
------------------------------
1000              computer-1000
1001              book-1001
1003              music-1003
:                  :
                   :
:                  :
:                  :
        
1500               Movie-1003

I was using the following Update statement ,but it didn't work.

Declare @x int
Declare @category varchar
set @x =  categoryid
begin
update category
set categoryname = @category + ' - ' +  @x
where categoryname = @category
End

Do I need to use a cursor?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-04 : 03:18:14

No you dont need a cursor

UPDATE Category
SET CategoryName = CategoryName + ' - ' + CategoryID

The mistake you were making was trying to use the variables, when you needed to directly use the column names in the SET clause.

OS

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-04 : 13:09:01

How about if I want to add 2000 after computer - categoryID.
So it look like:

CategoryID(PK)  Categoryname 
------------------------------
1000              computer-1000-2000
1001              book-1001-2001
1003              music-1003-2003
:                  :
                   :
:                  :
:                  :
        
1500               Movie-1500-2500

I am trying to use a variable,but it didn't work

declare @x int
set @x = 2000

while @x < 2500
begin
UPDATE Category
SET CategoryName = CategoryName + ' - ' + convert(varchar(20),@x)

@x= @x + 1
end
go
select * from category

what I am doing wrong?

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2003-05-04 : 13:26:11


UPDATE Category
SET CategoryName = CategoryName + '-' + CategoryID + '-'
cast(cast(CategoryID as int) + 1000 as varchar(4))

Edited by - LarsG on 05/04/2003 13:27:01

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources