Update a column based on the value on another column

I have an SQL table dbo.aTest. I want to update Col3Update if Col1 is AA and whatever value in Col2, copy that value in Col3Update from Col1 - values AA,BB,CC,DD,EE and next Col1 that starts with AA again. See attached.

I have a Cursor here that was answered by Ryan Chong. Thank you again Ryan Chong.

declare @uniqueid int;
declare @column2 nvarchar(100);
declare @last_column2 nvarchar(100);
declare @cnt int;

declare cur cursor for  
select uniqueid, [col2]
from dbo.aTest
where  [col1] = 'AA'  
order by uniqueid

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

set @last_column2 = ''
set @cnt = 0

while @@fetch_status = 0  
begin  
       if @last_column2 = '' or (len(@column2)> 1 and @cnt > 1)
			set @last_column2 = @column2
			
	   update a 
	   set Col3Update = @last_column2 
	   from dbo.aTest a
	   where uniqueid = @uniqueid
	   set @cnt = @cnt + 1
	   
       fetch next from cur into @uniqueid, @column2  
end 

close cur;
deallocate cur;

select * from dbo.aTest
order by uniqueid

Open in new window


What I did  wrong in this cursor?

Please help.

Thank you again for all your help.
SQL_and_OUTPUT_NUMBERS.xlsx
Queennie LAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
I don't see any need for a cursor.  I don't have actual, usable data to test with, but something like this should do it:

;WITH cte_base_column AS (
    SELECT UniqueId, ROW_NUMBER() OVER(ORDER BY UniqueId) AS row_num
    FROM dbo.aTest
    WHERE col1 = 'AA'
)
SELECT a.UniqueId, a.Col1, a.Col2, oa1.Col3Update
FROM dbo.aTest a
OUTER APPLY (
    SELECT TOP (1) row_num AS Col3Update
    FROM cte_base_column cbc
    WHERE a.UniqueId >= cbc.UniqueId
    ORDER BY UniqueId
) AS oa1
0
Queennie LAuthor Commented:
@ScottPletcher:

When the above query, this is the output


UniqueId      Col1      Col2      Col3Update
1      AA      1      1
2      BB      11111111111      1
3      CC      472      1
4      DD      AD      1
5      EE      6      1
6      AA      2      1
7      BB      22222222222      1
8      CC      472      1
9      DD      OA      1
10      EE      6      1


Thank you for your help.
0
Scott PletcherSenior DBACommented:
D'OH, sorry.  I didn't sort correctly in the OUTER APPLY:

OUTER APPLY (
    SELECT TOP (1) row_num AS Col3Update
    FROM cte_base_column cbc
    WHERE a.UniqueId >= cbc.UniqueId
    ORDER BY UniqueId DESC
) AS oa1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Queennie LAuthor Commented:
@ScottPletcher:
I will test this and I will get back with you monday.

Thank you again for help.

Have a good weekend.
0
Queennie LAuthor Commented:
@ScottPletcher:

Thank you again for all your help.

I really appreciate it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.