4

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Meni
  • 119
  • 1
  • 4
  • 13
  • 1
    Is there any trigger on this table? – Suraj Kumar Dec 21 '19 at 09:57
  • 1
    @Suraj_Kumar Yes, you are right. there was a trigger in place. After deleting the trigger it updated immediately. However now the software that is based on this DB fails to load. pfffff – Meni Dec 21 '19 at 10:10

2 Answers2

4

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

Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
  • Both columns are part from the same table. do i need to use INNER JOIN for updating columns within the same table? – Meni Dec 21 '19 at 09:29
  • @Meni You can do it by either join or without join also. I have updated my answer, please check. – Suraj Kumar Dec 21 '19 at 09:38
  • this is driving me crazy. i have tried both your methods and they produce the same results. the command passes through without errors. However all rows in column UserName becomes `sa` – Meni Dec 21 '19 at 09:49
  • Something is terribly wrong. i get the same result even if i try: `UPDATE test_table1 UserName = 'test' ` – Meni Dec 21 '19 at 09:55
  • Ya, you need to check if your query is producing the wrong result, however the way is correct. – Suraj Kumar Dec 21 '19 at 09:56
  • 1
    There was a trigger that was producing the undersired results. after deleting the trigger the table updated correctly. Thank you – Meni Dec 21 '19 at 10:08
1
UPDATE test_table1 SET UserName = test_table1.Kwd WHERE test_table1.id=some_id

Enter record id

Coder
  • 23
  • 8
  • I would like to update all rows in the table. i have tried specifying an id in order to update a specific ro and it produces the same result. – Meni Dec 21 '19 at 09:25