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


Ok, I would like to use aCASE 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 toUPDATE all position values that are great than the position value that was removed toposition - 1 on a per id_layout and id_layout_position basis.

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

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})';
    $query = 'id_module = {int:id_module}';

$request = $smcFunc['db_query']('', '
        id_position, id_layout_position, id_layout, position
    FROM {db_prefix}dp_module_positions
    WHERE ' . $query,
        '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'];


// 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})',
        '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
                position = position - 1
            WHERE position > {int:position} AND id_layout = {int:id_layout} AND id_layout_position = {int:id_layout_position}',
                '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 thisUPDATE 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 aFOREACH LOOP?




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){
    $infos = DB::getData('SELECT position FROM db_positions WHERE id_position = :ID', array(':ID' => $id));
      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']);
  catch(Exception $e){



To update with uninterrupted sequence you can use the following:

SET @reset := 0; 
UPDATE dp_positions
  positions = 
    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`()
SET @pos := -1;
SET @reset := (SELECT MIN(id_layout_position) FROM dp_position);
  position = 
      WHEN b > @reset THEN 
        IF(@reset:=id_layout_position, @pos:=0, @pos:=0)        
      ELSE @pos := @pos + 1    
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.

People are also looking for solutions to the problem: simplexml - PHP+MYSQL Server Config


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.