Update Sql column in Loop

Question

I have a table which has the following format :-

 Column A  Column B      Column C
   a          b             NULL
                            06/13/2012
                            08/11/2012
   C           D            NULL
                            09/11/2011
   E          F             NULL
                            06/13/2012
                            09/11/2012

Where as the required output needs to be

Column A  Column B      Column C
       a          b       06/13/2012
       a          b       08/11/2012                                     
       C          D       09/11/2011   
       E          F       06/13/2012   
       E          F       09/11/2012

Is there a way by which we can update column A and column B by loop. i.e. start with a combination of distinct Column A and Column B and update the remaining rows of that column till we reach a new distinct value. Something like Update column A and column B with a and b until a new values (C, D)

SQL tables represent unordered sets. I don't see a way to do what you want, unless you have a column that specifies the ordering of the rows. — Gordon Linoff
– Gordon Linoff, Commented Aug 11, 2017 at 14:32
That looks more like someone copy-pasted an Excel sheet with grouping. You are much better off fixing your input data then trying to fix this up after the fact in SQL. (Incidentally, even if the data isn't from Excel, filling in blank cells happens to be something Excel is good at.) Adding a row number makes it possible to do this in SQL, but still far from attractive. — Jeroen Mostert
– Jeroen Mostert, Commented Aug 11, 2017 at 14:40
Do you have an identity seed on this table, which may define the order? Then we can do the self join and can update the columns. But with design its impossible as mentioned in the above comments — Gopakumar N.Kurup
– Gopakumar N.Kurup, Commented Aug 11, 2017 at 15:06
I can help you with a script task in SSIS basically for anysource that loads in that order — KeithL
– KeithL, Commented Aug 11, 2017 at 15:50

TT. · Accepted Answer · 2017-08-13 17:26:31Z

INSERT INTO TableStack VALUES 
 (  'a',         'b',NULL)
,( '' ,'','06/13/2012')
,( '' ,'','08/11/2012')
,('C',           'D',NULL)
,( '' ,'','09/11/2011')
,('E',          'F',NULL)
,( '' ,'','06/13/2012')
,( '' ,'','09/11/2012')

--delete from TableStack


SELECT [Column A]
      ,[Column B]
      ,[Column C]
INTO #STACK 
FROM TableStack

ALTER TABLE #STACK
ADD ModifiedA varchar(10),
ModifiedB varchar(10)

declare @t varchar(10)=''
update t
set ModifiedA=@t,
@t=@t+[column A]
from #STACK t

declare @t varchar(10)=''
update t
set ModifiedB=@t,
@t=@t+[column B]
from #STACK t

SELECT *,case when len(ModifiedA)=2 then right(ModifiedA,1)
when len(ModifiedA)=3 then  right(ModifiedA,1) else ModifiedA end A,
case when len(ModifiedB)=2 then right(ModifiedB,1)
when len(ModifiedB)=3 then  right(ModifiedB,1) else ModifiedB end B
 INTO #StackNew
 FROM #STACK

 update s
 set s.[column a]=s.A,
 s.[Column B]=s.B
 from #StackNew s

 SELECT [column a],[Column B],[Column C] FROM #StackNew
 WHERE [Column C] is not null

Yogesh · Accepted Answer · 2017-08-11 14:49:50Z

If you have primary key or any unique indetification of row in your first table then you can store this in temp table and start loop through till new value comes

You can refer below query to get idea of how to do it

Select   ROW_NUMBER() OVER (ORDER BY ColumnC ) AS RowNumber,ColumnA,  ColumnB, ColumnC   --you are getting top table as asked in question
Into   #Temp
From   TableName

Declare @ColumnA VARCHAR(1) 
Declare @ColumnB VARCHAR(1) 
DECLARE @RowNumber INT

Declare @LastColumnA VARCHAR(1) 
Declare @LastColumnA VARCHAR(1) 


While (Select Count(*) From #Temp) > 0
Begin

    Select Top 1 @RowNumber = RowNumber, @ColumnA = ColumnA, @ColumnB=ColumnB  From #Temp  ORDER BY ColumnC

    IF ISNULL(@ColumnA,'') = ''
    BEGIN
      --update previous columns because its blank
      UPDATE TableName SET ColumnA= @LastColumnA, ColumnB = @LastColumnB WHERE -- if primary key is there use it else use all where condition below
      ColumnA = @ColumnA and ColumnB=@ColumnB and  ColumnC=@ColumnC
    END
    ELSE
    BEGIN
        SET @LastColumnA = @ColumnA;
     --Get New  columns because its blank
    END
    --Do some processing here

    Delete FROM #Temp Where RowNumber = @RowNumber

End

This query is not tested you can refer this for logic

Collectives™ on Stack Overflow

Update Sql column in Loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related