467,134 Members | 948 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

Moving Data from right to left SQL

Apologies if the title is a bit abstract, but it was hard to put into a single title what I am trying to achieve.

I have a table called PTYP and columns named PTYPNR, STAB1, STAB2, STAB3.......to STAB10

In some of the records one of the STAB columns (could be any of them) can have an entry of 1659 what I am trying to do is delete this entry but then move the values in the columns to the right 1 to the left

e.g Say the existing record layout is
PTYP STAB1 STAB2 STAB3 STAB4 STAB5............ STAB10
1 400 300 1659 700 0 0
should become
1 400 300 700 0 0.............. 0

what I am getting is
1 400 300 300

I am using the following query

update protel.ptyp set
[stab1]= case when [stab1]=1659 then [stab2] else [stab1] end
,[stab2]= case when [stab2]=1659 or [Stab2]= [stab1] then [stab3] else [stab2] end
,[stab3]= case when [stab3]=1659 or [Stab3]= [stab2] then [stab4] else [stab3] end
,[stab4]= case when [Stab4]=1659 or [Stab4]= [stab3] then [Stab5] else [Stab4] end
,[Stab5]= case when [Stab5]=1659 or [Stab5]= [stab4] then [Stab6] else [Stab5] end
,[Stab6]= case when [Stab6]=1659 or [Stab6]= [stab5] then [Stab7] else [Stab6] end
,[Stab7]= case when [Stab7]=1659 or [Stab7]= [stab6] then [Stab8] else [Stab7] end
,[Stab8]= case when [Stab8]=1659 or [Stab8]= [stab7] then [Stab9] else [Stab8] end
,[Stab9]= case when [Stab9]=1659 or [Stab9]= [stab8] then [Stab10] else [Stab9] end
,[Stab10]= case when [Stab10]=1659 or [Stab10]= [stab9] then 0 else [Stab10] end


Can anyone provide the correct code?
thank you
Nov 27 '08 #1
  • viewed: 777
Share:

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by KalleD | last post: by
reply views Thread by Voast | last post: by
1 post views Thread by cricketunes@yahoo.com | last post: by
5 posts views Thread by niklaus@gmail.com | last post: by
3 posts views Thread by Cartoper | last post: by
10 posts views Thread by cjparis | last post: by
1 post views Thread by rsteph | last post: by
15 posts views Thread by mcjason@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.