Query to shift 3 columns Data over one place?

[nstouffer]

Does anyone know a query that will shift the data in 3 columns over one place to the right.

Ex:

Seg1 Seg2 Seg3 Seg4
01 400300 700
01 400325 700

to

Seg1 Seg2 Seg3 Seg4
01 400300 700
01 400325 700


This is for large datasets...

thanks

 

[tlbroadbent]

Use an update query. It is fairly simple.

Update Yourtable With (tablockx holdlock)
Set Seg4=Seg3, Seg3=Seg2, Seg2=Seg1, Seg1=null

If the data set is large you should do the update in batches. Perhaps, you could use something like the following. Modify as needed for your process.

Set Rowcount 10000
Declare @rc int
Set @rc=1

While @rc>0
Begin

Begin Transaction

Update Yourtable With (tablockx holdlock)
Set Seg4=Seg3, Seg3=Seg2, Seg2=Seg1, Seg1=null
Where Seg1 Is Not Null

Set @rc=@@rowcount

Commit

End Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[nstouffer]

thanks so much!