Cursor does not update Level (SQL)

Question

Good night,

I am making a cursor that updates the table level column of schools with the following rules: a. Schools with more than three levels of education are graded with category A b. Schools with three levels of education are graded with category B c. Schools with two levels of education are graded with category C d. Other schools are graded with category D

The cursor is placing the result of the Level Column as category 'C' on all lines, I want to take the result of my SELECT and use it to add the categories in the Level column, follow the code so far:

-- Variáveis do Cursor
DECLARE @Nivel CHAR(1),
        @CodigoNivelEnsino INT,
        @Contador INT= 0;

-- Cursor que atualiza a coluna nível da tabela de escolas.

DECLARE cur_AttTbNivel CURSOR FOR
SELECT CodigoNivelEnsino,count(CodigoNivelEnsino) AS 'Quantidade de Escolas por Nivel de Ensino'
FROM Escola_NivelEnsino 
GROUP BY CodigoNivelEnsino
HAVING COUNT(CodigoNivelEnsino) > 1
ORDER BY COUNT(CodigoNivelEnsino) DESC

SELECT Nivel
FROM Escola

--Abrindo o cursor
OPEN cur_AttTbNivel;

-- Selecionar dados
FETCH NEXT FROM cur_AttTbNivel
INTO @Nivel, @CodigoNivelEnsino
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @CodigoNivelEnsino >3
    UPDATE Escola
    SET Nivel = 'A'
    IF @CodigoNivelEnsino = 3
    UPDATE Escola
    SET Nivel = 'B'
    IF @CodigoNivelEnsino = 2
    UPDATE Escola
    SET Nivel = 'C'
    ELSE
    UPDATE Escola
    SET Nivel = 'D'



    WHERE
        Nivel = @Nivel



    FETCH NEXT FROM cur_AttTbNivel INTO @Nivel, @CodigoNivelEnsino;
END

-- Fechando e desalocando o cursor da memória
CLOSE cur_AttTbNivel
DEALLOCATE cur_AttTbNivel

Data from the Tables in question:

Answer 1

Good morning Lucas.

First of all I ask you to always give as much code information as possible formatted as code (like creating the tables and entering the tables [which were in the image])

I had to deduce/create some field names to reproduce your problem.

Now about your cursor problem the problems were in:

• Where - see that your Where is only in the last condition, on Else.
• If, elseif and Else - The structure is incorrect making it always fall into Else in the end because it did not use elseif
• Key Data in Cursor - See that you are not tying the school you want to update.

I used the following tables and data:

create table NivelEnsino
(
CodigoNivelEnsino int identity(1,1)
,nomenivel varchar(60)
)

create table escola
(
idescola int identity(1,1)
,nomeescola varchar(60)
,nivel char(2)
)

create table escola_nivelensino 
(
idescola int
,CodigoNivelEnsino int
)

insert into nivelensino 
select 'ensino fundamental' 
union
select 'ensino medio' 
union
select 'ensino tecnico' 
union
select 'ensino superior' 

insert into escola
select 'FAQ1 - 74','1'
union
select 'FAQI - POA','4'
union
select 'QI - ALVORADA','3'
union
select 'Escolinha do professor raimundo','10'
union
select 'escola do rock','7'

insert into escola_nivelensino
select 1,3
union
select 2,3
union
select 2,4
union
select 3,1
union
select 3,2
union
select 3,3
union
select 4,1
union
select 4,2
union
select 4,3
union
select 5,1
union
select 5,2
union
select 5,3
union
select 5,4

and the cursor was as follows:

-- Variáveis do Cursor
DECLARE @idescola int,
        @CodigoNivelEnsino char(2)

-- Cursor que atualiza a coluna nível da tabela de escolas.
DECLARE cur_AttTbNivel CURSOR FOR
SELECT idescola
,count(CodigoNivelEnsino) AS 'Quantidade de nivel por escola'
FROM Escola_NivelEnsino 
GROUP BY idescola
ORDER BY COUNT(CodigoNivelEnsino) DESC

--Abrindo o cursor
OPEN cur_AttTbNivel;

-- Selecionar dados
FETCH NEXT FROM cur_AttTbNivel
INTO @idescola, @CodigoNivelEnsino
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @CodigoNivelEnsino >3
        begin
            UPDATE Escola
            SET Nivel = 'A'
            where idescola = @idescola
        end 
    Else IF @CodigoNivelEnsino = 3
        begin
            UPDATE Escola
            SET Nivel = 'B'
            where idescola = @idescola
        end
    Else IF @CodigoNivelEnsino = 2
        begin
            UPDATE Escola
            SET Nivel = 'C'
            where idescola = @idescola
        end
    ELSE
        begin
            UPDATE Escola
            SET Nivel = 'D'
            WHERE idescola = @idescola
        end


    FETCH NEXT FROM cur_AttTbNivel INTO @idescola, @CodigoNivelEnsino;
END

-- Fechando e desalocando o cursor da memória
CLOSE cur_AttTbNivel
DEALLOCATE cur_AttTbNivel

I hope I’ve helped ;)