CODEWITHSUNDEEP
sql-serversql-update
Meni
Meni

Reputation: 136

How to update a column with data from another column in SQL Server?

I have a SQL Server table with multiple columns 2 of which are Kwd and UserName. I am trying to update all values in column UserName with values from column Kwd. Both columns are of type nvarchar.

I am using the below query.

UPDATE test_table1 
SET UserName = Kwd

I have also tried

SET a.UserName = B.Kwd 
FROM test_table1 a 
INNER JOIN test_table1 b ON a.PelID = b.PelID

However it updates all column with value "sa", what could be wrong?

Update 1:

I have tried testing an easier approach to see what will happen When I run the query:

UPDATE test_table1 
SET UserName = 'test'

it still updates all rows with value sa

Upvotes: 4

Views: 11516

Answers (2)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this as shown below.

UPDATE a 
    SET a.UserName = b.Kwd
    FROM test_table1 a INNER JOIN test_table1 b ON a.Id= b.Id

You can also try the following query.

update test_table1 
   set test_table1.UserName = B.Kwd
  from test_table1 B

You can follow the link Inner join update in SQL Server

Here is an example with sample data.

create table test_table1 (PelID int, Kwd varchar(10), UserName varchar(10))
insert into test_table1 Values (1, 'A', 'B'), (2, 'K', 'P'), (3, 'N', 'S'), (4, 'G', 'H'), (5, 'T', 'F')

Select * from test_table1

UPDATE a 
    SET a.UserName = b.Kwd
    FROM test_table1 a INNER JOIN test_table1 b ON a.PelID = b.PelID

Select * from test_table1

update test_table1 
   set test_table1.UserName = B.Kwd
  from test_table1 B

Select * from test_table1

This output can be checked on the link

Upvotes: 4

Coder
Coder

Reputation: 23

UPDATE test_table1 SET UserName = test_table1.Kwd WHERE test_table1.id=some_id

Enter record id

Upvotes: 1

Related Questions