CODEWITHSUNDEEP
sqlsql-server
birgersp
birgersp

Reputation: 4916

How do you update table using local variables or user-defined functions?

I am trying to update my table with setting the "username" to be a product of "name".

Here is my example:

declare @username varchar(128)

-- create local table
declare @t table (name varchar(32), username varchar(32))
insert into @t(name, username) values ('Johnny Cash','')

-- update rows, set "username" to its correct value
;with mytable as (select * from @t)
update mytable
-- this is really messy, I want to use some local variables or a user-defined function here to compute the username...
set username = lower(substring(name, 0, charindex(' ', name)) + '.' + substring(name, charindex(' ', name)+1, len(name)))

-- select all rows so I can see them
;with mytable as (select * from @t)
select * from mytable

For each row, I want the username to be the same as name but with some modifications.

Is this possible?

To clarify, I want the row with name "Johnny Cash" to have a username "johnny.cash"

Upvotes: 0

Views: 134

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Simple update statement would also work, no need to use variable :

update @t
     set username = replace(lower(name), ' ', '.')

EDIT :

If you want only single value to be update with help of variable then you can do :

select @username = replace(lower(name), ' ', '.')
from @t
where name = @username;

Upvotes: 1

Related Questions