SQL: Using a CASE Statement to update 1000 rows at once

Question

Ok, I would like to use a CASE STATEMENT for this, but I am lost with this. Basically, I need to update a ton of rows, but just on the "position" column. I need to UPDATE all position values that are great than the position value that was removed to position - 1 on a per id_layout and id_layout_position basis.

OK, here is a pic of what the table looks like:

Now let's say I delete the circled row, this will remove position = 2 and give me: 0, 1, 3, 5, 6, 7, and 4. It should reposition the position values that are greater than 2, so that it looks like this: 0, 1, 2, 4, 5, 6, and 3.

OK, Here's what I got so far within the DELETING of the ROW, so here's how it looks for this (NOTE: $module_info['clones'] = an array of clones to delete, in the table above there would be none, because all id_clone values are 0, and $module_info['id'] is the id_module value that we are removing):

// Get rid of id_clone = 0, because we can't use them.
$module_info['clones'] = array_values(array_filter($module_info['clones']));

// Selecting the positions.
if (isset($module_info['clones'][0]))
    $query = 'id_module = {int:id_module} || id_clone IN ({array_int:id_clones})';
else
    $query = 'id_module = {int:id_module}';

$request = $smcFunc['db_query']('', '
    SELECT
        id_position, id_layout_position, id_layout, position
    FROM {db_prefix}dp_module_positions
    WHERE ' . $query,
    array(
        'zero' => 0,
        'id_module' => $module_info['id'],
        'id_clones' => $module_info['clones'],          
    )
);

while ($row = $smcFunc['db_fetch_assoc']($request))
{
    $module_info['position'][$row['id_layout']]['pos' . $row['id_position'] . $row['position'] . '_' . $row['id_layout_position']] = $row['position'];
    $module_info['id_positions'][] = $row['id_position'];
}

$smcFunc['db_free_result']($request);

// Remove all module and clone positions from the layout!
$smcFunc['db_query']('', '
    DELETE FROM {db_prefix}dp_module_positions
    WHERE id_position IN ({array_int:id_positions})',
    array(
        'id_positions' => $module_info['id_positions'],
    )
);

foreach($module_info['position'] as $id_layout => $id_layout_pos)
{
    foreach($id_layout_pos as $key => $position_val)
    {
        $lPos = explode('_', $key);
        $lPosId = (int) $lPos[1];

        $smcFunc['db_query']('', '
            UPDATE {db_prefix}dp_module_positions
            SET
                position = position - 1
            WHERE position > {int:position} AND id_layout = {int:id_layout} AND id_layout_position = {int:id_layout_position}',
            array(
                'id_layout' => (int) $id_layout,
                'position' => (int) $position_val,
                'id_layout_position' => $lPosId,
            )
        );
    }
}

NEW QUESTION: THERE's Just gotta be some sort of way to use a CASE STATEMENT in this UPDATE now. For Example, I now need to update all positions to position - 1, on a per id_layout, and per id_layout_position basis. BUT only where the position is greater than the current position for that id_layout and id_layout_position value.

Is there anyway to do this without using a FOREACH LOOP?

Answer 1

To update with uninterrupted sequence you can use the following:

SET @reset := 0; 
UPDATE dp_positions
SET
  positions = 
  CASE          
    WHEN id_layout_position > @reset THEN 
      IF(@pos:=0,NULL, @reset:=id_layout_position)        
    ELSE @pos := @pos + 1    
  END - 1  
ORDER BY id_layout_position, position;

Notes: @reset should be set to minimum value of id_layout_position, assuming that you are restarting the counter on id_layout_position change, if it is more complicated the WHEN condition will need to change and you might need more variables to hold the values from previous row.
Furthermore the IF is a hack, it will never be NULL as long you are setting @pos to 0 and not to some other starting value, I just didn't know how to force mysql to evaluate two expressions (anyone, is there something elegant there?)

The above is tested, but with different column/table names (retyping errors possible).

EDIT: My testing was not so good, the above has an error, and also seems OP needs to bypass security of a framework, so here's a correction and stored procedure version

Using phpmyadmin or mysql command line execute

delimiter //
CREATE PROCEDURE `renumerate`()
BEGIN
SET @pos := -1;
SET @reset := (SELECT MIN(id_layout_position) FROM dp_position);
UPDATE
  dp_positions
SET
  position = 
    CASE          
      WHEN b > @reset THEN 
        IF(@reset:=id_layout_position, @pos:=0, @pos:=0)        
      ELSE @pos := @pos + 1    
    END
ORDER BY id_layout_position, position;
END //
delimiter ;

(in case you use phpMyAdmin do not use delimiter statements but directly specify delimiter in the interface as //). When calling the procedure execute normal SQL

$smcFunc['db_query']('', 'CALL renumerate()');

Hope my testing was better this time.

Answer 2

Before you delete a row, you could get it's position and decrement the position of all rows which have a higher position.

Pseudo code :

function deleteRow($id){
  DB::startTransaction()
  try{
    $infos = DB::getData('SELECT position FROM db_positions WHERE id_position = :ID', array(':ID' => $id));
    if(empty($infos)){
      throw("useless ID");
    }
    DB::query('DELETE FROM db_positions WHERE id_position = :ID', array(':ID' => $id));
    DB::query('UPDATE db_positions SET position = position - 1 WHERE position > :position', array(':position' => $infos['position']);
    DB::commit();
  }
  catch(Exception $e){
    DB::rollBack();
  }
}