Update values from one column in same table to another in SQL Server

Question

I'm trying to overwrite values that are found in TYPE1 with values that are found in TYPE2.

I wrote this SQL to try it out, but for some reason it isn't updating:

select * from stuff

update stuff
set TYPE1 = TYPE2
where TYPE1 is null;

update stuff
set TYPE1 = TYPE2
where TYPE1 ='Blank';

http://www.sqlfiddle.com/#!3/a4733/17

Any reason why my values in TYPE1 are not updating?

score 72 · Accepted Answer · answered Apr 23 '13 at 23:06

72

This works for me

select * from stuff

update stuff
set TYPE1 = TYPE2
where TYPE1 is null;

update stuff
set TYPE1 = TYPE2
where TYPE1 ='Blank';

select * from stuff

answered Apr 23 '13 at 23:06

Sparky

14,967
2
31
45

1

Yep, you're right. But after running the update statements once, I would run the select statement again... but no change. Is there a new instance of a fiddle after every run? – neuquen Apr 23 '13 at 23:11
2

@Keven Yes, each SQLFiddle execution is independent of the previous run. – Adam Wenger Apr 24 '13 at 00:06

score 57 · Answer 2 · edited May 26 '16 at 19:29

57

UPDATE a
SET a.column1 = b.column2
FROM myTable a 
INNER JOIN myTable b
on a.myID = b.myID

in order for both "a" and "b" to work, both aliases must be defined

edited May 26 '16 at 19:29

hanzolo

1,151
1
10
18

answered Aug 07 '14 at 19:35

Bob Taylor

587
4
2

2

the accepted answer works, but this is is a bit more direct and to the point – hanzolo May 26 '16 at 18:38
I subtracted a point because this doesn't actually answer my original question. I wanted to update a value from `TYPE1` with the value from `TYPE2` IN THE SAME TABLE. – neuquen Nov 22 '16 at 21:07
6

Actually the example above answers the question because the JOIN is done on the same table as the FROM statement, if you look carefully "myTable" is referenced twice. It's just a little more verbose than the accepted answer. – mastazi Mar 08 '17 at 04:17
@mastazi Thanks for pointing that out! I completely glossed over the fact that it was the same table. – neuquen Jun 13 '17 at 01:11
2

This is the best answer for sure. This is very useful when you're taking data in a column and manipulating it before putting in another column. For example I'm taking my PART_NUMBER column and removing the dashes from it and placing it in MOD_PART_NUMBER. Best way to do this! – Michael Fever Aug 28 '19 at 14:56

score 37 · Answer 3 · answered Sep 12 '17 at 11:50

37

UPDATE TABLE_NAME SET COLUMN_A = COLUMN_B;

Much easier. At least on Oracle SQL, i don't know if this works on other dialects as well.

answered Sep 12 '17 at 11:50

Győri Sándor

592
5
10

3

Yeah, works for SQL Server as well (so your answer is perfectly valid for this question). – Jacob van Lingen Feb 09 '18 at 10:30
1

I got error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. – Danniel Little Feb 21 '18 at 17:04
1

This should be the answer. Worked for me. With huge concat of 5 columns into one field. – JustJohn Mar 22 '20 at 01:12
worked in postgres. Also it easy to combine with another query like `UPDATE table_name SET column_1 = cast(split_part(column_2, ' ', 1) as float);` – Hermawan Wiwid Jun 05 '20 at 02:46
simple and works – MMezlini Nov 29 '21 at 15:22

score 3 · Answer 4 · answered Apr 23 '13 at 23:06

3

You put select query before update queries, so you just see initial data. Put select * from stuff; to the end of list.

answered Apr 23 '13 at 23:06

Jack

1,892
1
19
23

score 3 · Answer 5 · edited May 14 '18 at 10:33

3

This answer about updating column from a part of another column in the same table.

update T1
set domainname = (New value) --Example: (SELECT LEFT(TableName.col, CHARINDEX('@',TableName.col)-1) STRIPPED_STRING FROM TableName where TableName.col = T2.Emp_ID)
from TableName T1
INNER JOIN
    TableName T2
ON 
    T1.ID= T2.ID;

edited May 14 '18 at 10:33

MBT

21,733
19
84
102

answered May 14 '18 at 08:42

Ahmed Mahmoud AbdElbaset

39
4

the safest solution – seyed ali ziaei Apr 29 '21 at 10:37

score 2 · Answer 6 · edited Apr 21 '19 at 10:32

2

update TABLE_1 a set COLUMN_1 = (select COLUMN_2 from TABLE_1 b where a.ID = b.ID)

edited Apr 21 '19 at 10:32

Zoe

27,060
21
118
148

answered Apr 21 '19 at 10:22

Tarek.Iraqi

21
1
2

`SET or WITH expected, got 'a'` – Greg Oct 23 '19 at 15:33

score 1 · Answer 7 · answered Apr 23 '13 at 23:06

1

Your select statement was before the update statement see Updated fiddle

answered Apr 23 '13 at 23:06

Ian Kenney

6,376
1
25
44

score 0 · Answer 8 · edited Jul 02 '20 at 00:27

0

UPDATE `tbl_user` SET `name`=concat('tbl_user.first_name','tbl_user.last_name') WHERE student_roll>965

edited Jul 02 '20 at 00:27

Ersoy

8,816
6
34
48

answered Jul 02 '20 at 00:03

selimhossain

139
1
7

score 0 · Answer 9 · edited Dec 16 '22 at 15:05

0

If you need to make an operation on a specific column:

update courses set hours = days * 5

edited Dec 16 '22 at 15:05

CristisS

1,103
1
12
31

answered Dec 12 '22 at 09:28

AhmAD zAke

1

score 0 · Answer 10 · answered Feb 08 '23 at 06:46

0

You can simply do this .

Update TableName SET Column1=Column2 where id=id

answered Feb 08 '23 at 06:46

Md Mahafuzur Rahman

21
2

Update values from one column in same table to another in SQL Server

10 Answers10

Linked

Related