mysql – How to make an UPDATE in a query that is conditioned by a WHERE and in turn by a limited by LIMIT?

Question:

I need help with a query limit. What I am trying to do is update a table of Students so that each student is automatically assigned a section according to a limit set by the admin user.

I have already tried to use the between function but I failed!

// IT DOES NOT WORK LIKE THIS

$consulta = "UPDATE `estudiantes` SET `seccion` = 'A' 
WHERE `EtapaGradoGrupo` = '5° Año' LIMIT 20,15; "; 

// SO IT WORKS but it updates all the registers from 1 to 20 and I want it to start updating from register 15 to register 20, that is, only 5 not the 20

$consulta = "UPDATE `estudiantes` SET `seccion` = 'A' 
WHERE `EtapaGradoGrupo` = '5° Año' LIMIT 20;"; 

I would like that Each student of a specific "Year N°" update their section to "A", "B", "C"… But in the query select Only the students of said "Year N°" Limited By the number of students per section that the user selected Note: The query works only when the limit has a single parameter but when I add the second parameter to include the position from which the section will start updating it gives me an IMG error with the error what gives me MySql


Try doing the following NOTE: I am learning how to use StackOverflow

require("datos_de_conexion.php");
$conexion = mysqli_connect( $direccion , $usuario , $password ,$servidor);
mysqli_set_charset($conexion ,"utf8");

$consulta = "SELECT*FROM`estudiantes`WHERE`Etapa/Grado/Grupo`='5°Año'LIMIT 
5, 5";
$resultado = mysqli_query( $conexion , $consulta );

while( $fila = mysqli_fetch_array( $resultado , MYSQLI_ASSOC ) ){

$IDE = $fila["ExpedienteN°"];

$Actualizar = "UPDATE `estudiantes` SET `seccion` = 'Z' WHERE 
`ExpedienteN°` = $IDE;";
$resultado_2 = mysqli_query( $conexion , $Actualizar );

}

echo "Fueron afectadas " . $filasAfectadas = mysqli_affected_rows( $conexion 
);

*Generates the query perfectly BUT when I execute the query inside the while it only affects 1 row, that is, the cycle should do 5 turns but it only does 1 if I print on the screen this is what appears:

UPDATE `estudiantes` SET `seccion` = 'Z' WHERE `ExpedienteN°` = 6; 
UPDATE `estudiantes` SET `seccion` = 'Z' WHERE `ExpedienteN°` = 7; 
UPDATE `estudiantes` SET `seccion` = 'Z' WHERE `ExpedienteN°` = 8; 
UPDATE `estudiantes` SET `seccion` = 'Z' WHERE `ExpedienteN°` = 9; 
UPDATE `estudiantes` SET `seccion` = 'Z' WHERE `ExpedienteN°` = 10; 
Fueron afectadas -1

Since the queries are generated correctly in each cycle, but only the first one with ID = 6 is executed!

Answer:

You could try using BETWEEN which works for:

Perform your update with records from a specific data range, in this case all id starting from 15 to 20.

A) Yes:

$consulta = "UPDATE `estudiantes` SET `seccion` = 'A' 
             WHERE `EtapaGradoGrupo` = '5° Año' 
             AND id BETWEEN 15 AND 20"; 

If you now do:

SELECT * FROM estudiantes 
LIMIT 14, 20;

It will show you all the records from 15 to 20 with the updated values ​​with the updated seccion column.

Scroll to Top