Changing the priority of a record can be thought of as the succession of the following events:

UPDATE ACT_Agenda
  SET prioridad = prioridad - 1 
  WHERE prioridad > ?

UPDATE ACT_Agenda
  SET prioridad = prioridad + 1 
  WHERE prioridad >= ?

Solution:

For security reasons it would be optimal to use prepared statements , so the code could be as follows:

<?php
//update.php
include_once('../include/conexion.php');

// Validamos que el registro existe y 
// obtenemos la prioridad que tiene
$query = "SELECT prioridad 
    FROM ACT_Agenda 
    WHERE id_agenda = ?";
$stmt = mysqli_prepare($con, $query);
mysqli_stmt_bind_param($stmt, "i", $_POST["pk"]);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);

if (mysqli_stmt_num_rows($stmt) == 1) {

    mysqli_stmt_bind_result($stmt, $prioridadAnt);
    mysqli_stmt_fetch($stmt);

    // Si se modifico la prioridad
    if ($prioridadAnt != $_POST['value']) {

        // Actualizamos todos los registros cuya prioridad
        // sea mayor a la prioridad anterior (1)
        $query = "UPDATE ACT_Agenda
          SET prioridad = prioridad - 1 
          WHERE prioridad > $prioridadAnt";
        mysqli_query($conn, $query);

        // Actualizamos todos los registros cuya prioridad
        // sea mayor igual a la nueva prioridad (2)
        $query = "UPDATE ACT_Agenda
          SET prioridad = prioridad + 1 
          WHERE prioridad >= ?";
        $stmt = mysqli_prepare($con, $query);
        mysqli_stmt_bind_param($stmt, "i", $_POST["value"]);
        mysqli_stmt_execute($stmt);
    }

    // Actualizamos la prioridad
    $query = "UPDATE ACT_Agenda 
        SET prioridad = ?
        WHERE id_agenda = ?";
    $stmt = mysqli_prepare($con, $query);
    mysqli_stmt_bind_param($stmt, "ii", $_POST["value"], $_POST["pk"]);
    mysqli_stmt_execute($stmt);
}

It is necessary to bring the previous value to be able to do it correctly.

If the extension you are using is x-editable, you can do it this way link

If you have no way to bring it or you want to make it more secure from the backend, you can bring it with a SELECT before doing the UPDATE

//Aquí iría el select

$query = "
 UPDATE ACT_Agenda SET ".$_POST["name"]." = '".$_POST["value"]."'
 WHERE id_agenda = '".$_POST["pk"]."'";

If the new value is less than the previous one, you have to add one to all those that are greater or equal to the previous number, if on the contrary the new value is less than the new one you have to subtract 1 from all those that are smaller up to the previous number

Basically

A = valor nuevo
B = valor anterior
V = valor de otro registro

Si A < B
   Suma 1 si V >= A && V < B;

Si A > B
   Resta 1 si V < A && V >= B

Example

-----------------------
|prioridad|actividad  |
|---------|-----------|
|   1     |act4       |
|   2     |act1       |
|   3     |act2       |
|   2     |act3       | //act3 paso a prioridad 2, la anterior era 4
|   5     |act5       |
|---------|-----------|

Then, as I mentioned, the new value is lower than the previous one, so we have to add 1 to all the values greater than and equal to 2 and less than 4

In this case, it would only be added to priority 2, converting it to 3 and priority 3 converting it to 4

EXAMPLE CASE CONTRARY

 -----------------------
|prioridad|actividad  |
|---------|-----------|
|   1     |act4       |
|   4     |act1       | //act1 paso a prioridad 4, la anterior 2
|   3     |act2       |
|   4     |act3       |
|   5     |act5       |
|---------|-----------|

As now the new number is greater than the previous one, we subtract all the numbers that are greater than 2 and less than or equal to 4; in this case it would only be subtracted from 3, converting it to 2 and to 4 converting it to 3

In your database, update directly in the query from the php,

<?php
//update.php
include_once('../include/conexion.php');

$query = "
 UPDATE ACT_Agenda SET ".$_POST["name"]." = '".$_POST["value"]."'
 WHERE id_agenda = '".$_POST["pk"]."'";
$resultt=mysqli_query($conn, $query);
if($resultt && $_POST["name"] == 'prioridad'){
   if($valorAnterior < $_POST["value"])
       $query2 = "UPDATE ACT_Agenda SET prioridad = prioridad + 1
     WHERE id_agenda <> $_POST['pk'] AND prioridad BETWEEN $valorAnterior AND $_POST['value']";
   else
       $query2 = "UPDATE ACT_Agenda SET prioridad = prioridad - 1
     WHERE id_agenda <> $_POST['pk'] AND prioridad BETWEEN $valorAnterior AND $_POST['value']";


   mysqli_query($conn, $query2);
}
if($resultt) {
  echo "YES";
 } else {
  echo "NO";
}
?>

Where $ previous value is the previous value mentioned at the beginning, whether you can bring it from the POST in the form or from the SQL query, it is up to you. p>

NOTE : I did this with sql direct since that is your query so that you could understand it, but this form is not recommended, you should look for and read about prepared queries, since it is the form adequate to make sql queries

Hello this would work for you, I have added a class for the connection to the database, I imagine you will have yours, but as I have done using this file that had already done, I have put it, if not You understand something.

<?php
        $activityToUpdate = 5;
        $id_agenda;
        $row = 2;
        $servidor = "localhost";
        $user = "root";
        $password = "******";
        $baseDatos = "AGENDA";
        require_once '../conexionMysqli.php';
        $agenda = new ServidorBaseDatos($servidor, $user, $password, $baseDatos);
        $agenda->consulta("SELECT * FROM act_agenda WHERE ID_AGENDA !=".$activityToUpdate." ORDER BY PRIORIDAD ASC");
        $update = new ServidorBaseDatos($servidor, $user, $password, $baseDatos);
        echo '<p>ACTIVIDAD: '.$activityToUpdate." PRIORIDAD: 1</p>";
        while ($fila = $agenda->extraerRegistro()) {
                    foreach ($fila as $indice => $valor) {
                        switch ($indice){
                            case 'ID_AGENDA':
                                $id_agenda = $valor;
                                break;
                        }
                    }
                    $update->consulta("UPDATE act_agenda SET PRIORIDAD=".$row." WHERE ID_AGENDA=".$id_agenda);
                    echo '<p>ID_AGENDA: '.$id_agenda." PRIORIDAD: ".$row."</p>";
                    $row += 1; 
                }
                $update->consulta("UPDATE act_agenda SET PRIORIDAD=1 WHERE ID_AGENDA=".$activityToUpdate);
                $update->cerrarConexion();
                $agenda->cerrarConexion();
        ?>

This would be another way to do it:

   <?php
    $activityToUpdate = 10;
    $row = 1;
    $servidor = "localhost";
    $user = "root";
    $password = "*******";
    $baseDatos = "AGENDA";
    require_once '../conexionMysqli.php';
    $agenda = new ServidorBaseDatos($servidor, $user, $password, $baseDatos);
    $agenda->consulta("SELECT * FROM act_agenda ORDER BY PRIORIDAD ASC");
    $id_agenda;
    $prioridad;
    $actividad;
    while ($fila = $agenda->extraerRegistro()) {
                $update = new ServidorBaseDatos($servidor, $user, $password, $baseDatos);
                foreach ($fila as $indice => $valor) {
                    switch ($indice){
                        case 'ID_AGENDA':
                            $id_agenda = $valor;
                            break;
                        case 'PRIORIDAD':
                            $prioridad = $valor;
                            break;
                        case 'ACTIVIDAD':
                            $actividad = $valor;
                            break;
                    }
                }
                if($id_agenda==$activityToUpdate && $prioridad==1){
                    $row =0;
                } else if($id_agenda==$activityToUpdate && $row>0) {
                    $prioridad = 1;
                    $update->consulta("UPDATE act_agenda SET PRIORIDAD=".$prioridad." WHERE ID_AGENDA=".$activityToUpdate);
                } else if($prioridad<=$row && $row>0){
                    $row = $row + 1;
                    $update->consulta("UPDATE act_agenda SET PRIORIDAD=".$row." WHERE ID_AGENDA=".$id_agenda);   
                }
                echo '<p>INDICE: '.$id_agenda." PRIORIDAD: ".$prioridad." ACTIVIDAD: ".$actividad."</p>";
                $update->cerrarConexion();
            }
            $agenda->cerrarConexion();
    ?>

This is the class that I used for the connection of the database:

<?php

header("Content-type: text/html; charset=utf-8");

class ServidorBaseDatos {

    private $servidor;
    private $usuario;
    private $password;
    private $baseDatos;
    private $descriptor;

    function __construct($servidor, $user, $password, $baseDatos) {
        $this->servidor = $servidor;
        $this->usuario = $user;
        $this->password = $password;
        $this->baseDatos = $baseDatos;
        $this->conectarBaseDatos();
    }

    private function conectarBaseDatos() {
        $this->descriptor = mysqli_connect($this->servidor, $this->usuario, $this->password, $this->baseDatos);
    }

    public function consulta($consulta) {
        $this->resultado = mysqli_query($this->descriptor, $consulta);
    }

    public function extraerRegistro() {
        $fila = mysqli_fetch_array($this->resultado, MYSQLI_ASSOC);
        if ($fila) {
            return $fila;
        } else {
            return false;
        }
    }

    public function cerrarConexion() {
        mysqli_close($this->descriptor);
    }

}

You could also make a function within the DatabaseServer class, so you can access the function from any part of the application.

Try the following update:

$consulta = '
update ACT_AGENDA
set prioridad = case 
  when prioridad < (select prioridad 
                    from (SELECT * FROM ACT_AGENDA) as t 
                    where id_agenda = '.$_POST['pk'].') 
       and prioridad >= '.$_POST['value'].'
    then prioridad + 1
  when prioridad > (select prioridad 
                    from (SELECT * FROM ACT_AGENDA) as t 
                    where id_agenda = '.$_POST['pk'].') 
       and prioridad <= '.$_POST['value'].'
     then prioridad - 1
  when id_agenda = '.$_POST['pk'].' then '.$_POST['value'].'
  else prioridad
end
';

In this link you have an example of that same update. In the example, I change category 5: from the original value 5 to the new value 2

You would have to UPDATE ar the whole table with the new ordered values, one way to achieve it is with a query that returns the priorities changed leaving space between the priorities to put the new priority there.

A. the SELECT query

To achieve this we first make a SELECT that returns the prioridad original * 2 , so there is room to put the element that changes. Special case is the activity that changes its priority, for this it returns nueva prioridad * 2 - 1 if the new priority is lower and nueva prioridad * 2 + 1 if it is greater or equal (to the current priority). This difference is to fall before or after the activity that "replaces".

an example to see it clearer:

Change act5 to priority 3 :

-------------------------------
|prioridad|actividad|prioridad|
|original |         |temporal |
|---------|---------|---------|
|   1     |act1     |    2    |  <- original(1) * 2 -> 1*2 = 2
|   2     |act2     |    4    |
|   3     |act3     |    6    |  <- original(3) * 2 -> 3*2 = 6
|   4     |act4     |    8    |
|   5     |act5     |    5    |  <- original(5) >= nueva(3) -> nueva * 2 - 1 -> 3*2-1 = 5
|---------|---------|---------|

When ordering by prioridad temporal : 2, 4, 5 , 6, 8

-------------------------------
|prioridad|actividad|prioridad|
|original |         |temporal |
|---------|---------|---------|
|   1     |act1     |    2    |  <- prioridad original * 2
|   2     |act2     |    4    |
|   5     |act5     |    5    |  <- nueva prioridad * 2 - 1 
|   3     |act3     |    6    |
|   4     |act4     |    8    |
|---------|---------|---------|

We added a counter to obtain a range of priorities between 1 and 5:

-----------------------------------------------
|prioridad| actividad | prioridad | prioridad |
|original |           | temporal  | nueva     |
|---------|-----------|-----------|-----------|
|   1     |act1       |     2     |     1     |      
|   2     |act2       |     4     |     2     |
|   5     |act5       |     5     |     3     |
|   3     |act3       |     6     |     4     |
|   4     |act4       |     8     |     5     |
|---------|-----------|-----------|-----------|

Written in SQL :

SET @actividad = 'act5';
SET @prioridad = 3;

SELECT
  prioridad AS prioridadoriginal,
  actividad,
  CASE
    WHEN (actividad = @actividad)
       THEN IF (
       prioridad >= @prioridad,
       @prioridad * 2 - 1,
       @prioridad * 2 + 1 )
    ELSE (prioridad * 2)
  END AS prioridadtemporal,
  @rank:=@rank+1 AS prioridadnueva
FROM ACT_Agenda
ORDER BY prioridadtemporal ASC

See it working: link

B. the UPDATE

To do the UPDATE based on a SELECT you have to do a JOIN , something like this:

SET @actividad = 'act5';
SET @prioridad = 3;

SET @rank=0;
UPDATE ACT_Agenda A
LEFT JOIN (
  SELECT 
    actividad, 
    CASE 
    WHEN (actividad = @actividad)
      THEN IF (
        prioridad >= @prioridad,
        @prioridad * 2 - 1,
        @prioridad * 2 + 1 )
      ELSE (prioridad * 2)
    END AS prioridadtemporal,
    @rank:=@rank+1 AS prioridadnueva
  FROM ACT_Agenda
  ORDER BY prioridadtemporal ASC
  ) AS T on T.actividad = A.actividad
SET A.prioridad = T.prioridadnueva

See it working: link

C. the PHP

To make the index ( @rank ) start in 0 you have to make two queries: the SET and the UPDATE , put everything in a single query does not work unless you use multi_query but there we can not binde the parameters as prepared statement. It would be something like this:

$actividad = "act5"; $prioridad = intval(3);

// Multiple queries
$query1 = "SET @rank=0";
$query2 = "
UPDATE ACT_Agenda A
LEFT JOIN (
  SELECT
    prioridad AS prioridadoriginal,
    actividad,
    CASE
      WHEN (actividad = ?)
       THEN IF ( prioridad >= ?, ? * 2 - 1, ? * 2 + 1 )
      ELSE (prioridad * 2)
    END AS prioridadtemporal,
    @rank:=@rank+1 AS prioridadnueva
  FROM ACT_Agenda
  ORDER BY prioridadtemporal ASC
  ) AS T on T.actividad = A.actividad
SET A.prioridad = T.prioridadnueva
";
// Query 1 : SET @rank
$stmt1 = $mysqli->stmt_init();
$stmt1->prepare($query1);
$stmt1->execute();
$stmt1->store_result();
$stmt1->close();
// Query 2 : prepare & bind params
$stmt = $mysqli->stmt_init();
if ($stmt->prepare($query2)) {
  $stmt->bind_param("siii", $actividad, $prioridad, $prioridad, $prioridad);
  $stmt->execute();
}
$stmt->close();
$mysqli->close();

Note: Due to limitations in the way of doing the binding, you must repeat $prioridad to fill in all the occurrences.