Update a column based on value on another column in SQL table

just wondering if there's a common column available to "group" your records together?

@Ryan:

I don't think there is.

I tried it with filename but it did not give me the right results.

Thank you.

not like this but probably got to do it using Cursor, like:

declare @uniqueid int;
declare @column1 nvarchar(100);
declare @last_column1 nvarchar(100);
declare @cnt int;

declare cur cursor for  
select uniqueid, [column 1]
from Table1
order by uniqueid, RowNumber

open cur  
fetch next from cur into @uniqueid, @column1

set @last_column1 = ''
set @cnt = 0

while @@fetch_status = 0  
begin  
       if @last_column1 = '' or (len(@column1)> 2 and @cnt > 1)
			set @last_column1 = @column1
			
	   update a set updatecolumn = @last_column1 
	   from Table1 a
	   where uniqueid = @uniqueid
	   set @cnt = @cnt + 1
	   
       fetch next from cur into @uniqueid, @column1  
end 

close cur;
deallocate cur;

select * from Table1
order by uniqueid

Select allOpen in new window

@Ryan Chong:

Thank you again for your help.

I will run and test and will get back to you.

Thank you.

Ryan, hope this helps; I made a dynamic range of the records, but you would probably know how to tie these into an update.

I figure the logic would be if the Usage != 'R' rows is BETWEEN L and H, then SET UpdateColumn with the range's Column1:

  SELECT y1.`UniqueID` AS `L`, y2.`UniqueID`-1 AS `H`, y1.`Column1` FROM
  (
   SELECT `UniqueID`, `Column1`
   FROM Table1
   WHERE `USAGE` = 'R'
  ) y1
  LEFT JOIN
  (
   SELECT `UniqueID`, `Column1`
   FROM Table1
   WHERE `USAGE` = 'R'
    UNION ALL
   SELECT * FROM
   (
    SELECT `UniqueID`, `Column1`
    FROM Table1
    ORDER BY `UniqueID` DESC
    LIMIT 0,1
   ) t2
  ) y2
  ON 
   y2.`UniqueID` = (SELECT MIN(`UniqueID`) FROM Table1 WHERE `USAGE` = 'R' AND y1.`UniqueID` < `UniqueID`)

Select allOpen in new window

L      H      Column1
16      28      AAA00208667301
29      39      AAA00208672801
40      50      AAA00208640201
51      82      AAA00207582801
83      (null)      AAA00208384701

http://sqlfiddle.com/#!9/714cf9/85

@Ryan Chong:

I like the cursor syntax. It is perfect. It works but when I run and check it. I did not realize some of [Column 1] has data with semicolon and numbers. I tried to change the data types but it did not work.

Please see attached.

Thank you again for your help.
SQL_Table_and_Output_Test.xlsx

@NerdsOfTech,

not able to test for your suggestion as the database specified is MS SQL not MySQL.

@Ryan Chong:


It works.

Thank you again for your help.