|
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
| 
