Move data from one column to another in the same table 3

[nathanharcup]

Hi All,
I hope you can help me out with this problem, I have several columns in a table, I want to move the data from column x to column y (I need to move the value from column x to column y for the same row) like the below;

I have;
Column x column y
Value1 <null>
value2 <null>

I need to end up with the following;
Column x column y
Value1 value1
value2 value2

I tried using DTS, however it just adds new rows to the table with every other column null apart from column y.

I sure this must be simple but just can't figure it out :-(

Thanks
Nathan

 

[cLFlaVA]

update table_name
   set column_y = column_x

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[vongrunt]

Star-worthy question for everyone: how to swap two columns in the same table?

Identical column definitions are assumed (data type/scale/prec/blah) - and both accept NULLs.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[cLFlaVA]

declare a variable.
use a cursor.
select all from table.
loop through cursor.
store column 1 in variable.
set column 1 value to column 2 value.
set column 2 value to variable value.

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[vongrunt]

Er... no cursors. Slooow.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[cLFlaVA]

i don't recall seeing that specification in the original question

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[nathanharcup]

Thanks you very much, i will run this now

 

[vongrunt]

> i don't recall seeing that specification in the original question

That would be like giving a star for sorting array by using bubblesort.

Swapping with temporary holder variable is OK and kinda obvious... now try the same without cursors.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[cLFlaVA]

vongrunt:

select * from t1;

create table #tmp (
  pk int,
  col1 int
)

insert into #tmp
  select pk, col1 from t1;

update t1 set col1 = col2;
update t1 set col2 = (select col1 from #tmp where pk = t1.pk);

drop table #tmp;

select * from t1;

output:

col1    col2    pk
------------------
1	2	1
3	4	2
5	6	3
7	8	4
9	10	5


col1    col2    pk
------------------
2	1	1
4	3	2
6	5	3
8	7	4
10	9	5

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[vongrunt]

Er #2... no temp tables. Too much memory

(Hint: one UPDATE statement + one variable are enough).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[cLFlaVA]

thanks for making me learn!

select * from t1;

declare @temp int;

update t1
   set @temp = col1,
       col1 = col2,
       col2 = @temp;

select * from t1;

col1    col2    pk
------------------
2	1	1
4	3	2
6	5	3
8	7	4
10	9	5


col1    col2    pk
------------------
1	2	1
3	4	2
5	6	3
7	8	4
9	10	5

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[amrita418]

Am I missing something? Isn't a straight update enough??

create table #testtable (ID int, Name varchar(40), Value varchar(40))

insert into #testtable values (101,    NULL,    NULL)
insert into #testtable values (101,    'Run',    'Test')
insert into #testtable values (101,    'Column1',    'Column2')
insert into #testtable values (102,    'Dummy',    'Dummy2')

select * 
from 	#testtable

update 	#testtable
set 	Name = Value,
	Value = Name

select 	* 
from 	#testtable

drop 	table #testtable

Regards,
AA

 

[cLFlaVA]

AA,

have you tried that?

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[vongrunt]

> Am I missing something? Isn't a straight update enough??

No you are not missing anything. I was trying to direct discussion and finally post the same thing - and then my ISP link went nuts (World Wide Wait ).

This example nicely illustrates how simple problems in SQL can get... simple. In procedural languages that simple swap would not be possible.

Anyway, two stars.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)