data transfer from one column to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

data transfer from one column to another

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2006-10-25 : 03:52:13

I have one table
col1 col2 col3
1 5 2.00
2 3 null
3 2 null
4 1 3.00
5 2 4.00

I want to swipe the data from col2 to col3 and after swiping the data the
col2 should be null
the o/p should be as a follow

col1 col2 col3
1 Null 5
2 null 3
3 null 2
4 null 1
5 null 2

and the query will execute many times so how i can track this?

please help me....

SQLTeam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 04:06:04

Have you tried yourself?

-- Prepare test data
declare @test table (col1 int, col2 int, col3 float)

insert	@test
select	1, 5, 2.50 union all
select	2, 3, null union all 
select	3, 2, null union all
select	4, 1, 3.00 union all
select	5, 2, 4.00

select * from @test

-- Do the work
update @test set col2 = null, col3 = col2

select * from @test

Peter Larsson
Helsingborg, Sweden

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-25 : 04:12:43

track as in save in another table with datestamp and who did the swipe so you can audit?

--------------------
keeping it simple...

shubhada
Posting Yak Master

117 Posts

Posted - 2006-10-25 : 04:40:32

but suppose 1st time swiping is done properly and suppose i executed the script again the data will be same as privious(revert back).so how i can track this thing

SQLTeam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 04:48:00

No, the data will NOT be reverted. This is because you set Col2 to NULL!

The data in Col2 will be swiched forth and back between NULL and the value for Col3.
The data in Col3 will be swiched forth and back between NULL and the value for Col2.

Peter Larsson
Helsingborg, Sweden

shubhada
Posting Yak Master

117 Posts

Posted - 2006-10-25 : 04:53:22

Actually my table have lots of data and i wan to swipe the data for specific condition
for ex -
col1 col2 col3 col4
1 5 2.00 a
2 3 null a
3 2 null b
4 1 3.00 c
5 2 4.00 a
only for a i want to swipe the data .in this case is it working the above solution?
suppose i have used above solution the how i can update the data ?
please help me ....

SQLTeam

shubhada
Posting Yak Master

117 Posts

Posted - 2006-10-25 : 05:12:50

I have trid ur solution but when i executed the query 2d time the o/p is
both column have null value

SQLTeam

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-25 : 05:21:04

because col2 is now null which you are putting in col3, then you nullify col2

what you need is another table to save the pk of the record that undergone "swapping", prevent swapping from occuring if the record exists in that table

does swapping occurs once? or is this like a password table where last n-passwords are not allowed to be recycled?

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 05:21:25

Of course! You explicit tell one column to be NULL. And one other column to be the old value for this column.
Next time, the other column gets the value from first column, which is NULL! And the first column is once again set to NULL.

If you just want to switch places, use

update @test set col2 = col3, col3 = col2

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 05:22:40

We can't help you, unless you tell us what you are trying to achieve.
If you don't, you are on your own and over your head with this.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources