SQL：使用 CASE 语句一次更新 1000 行

Question

好的，我想为此使用 CASE STATMENT，但我对此迷失了。基本上，我需要更新大量行，但只更新“位置”列。我需要在每个 id_layout 和 id_layout_position 的基础上更新所有大于被删除到position - 1的位置值的位置值。

好的，这是桌子的样子的图片： 替代文本 http://acs.graphicsmayhem.com/images/dp_positions_table.png

现在假设我删除了圈出的行，这将删除position = 2并给我：0、1、3、5、6、7和4。它应该重新定位大于2的位置值，使其看起来像this: 0, 1, 2, 4, 5, 6, and 3.

好的，这是我到目前为止在删除行中得到的内容，所以这是它的查找方式（注意：$module_info['clones'] =要删除的克隆数组，在上表中没有，因为所有 id_clone 值都是 0，而 $module_info['id'] 是我们要删除的 id_module 值）：

// 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,
            )
        );
    }
}

新问题： 现在必须有某种方式在此 UPDATE 中使用 CASE 语句。例如，我现在需要根据每个 id_layout 和每个 id_layout_position 将所有位置更新为位置 - 1。但仅当位置大于 id_layout 和 id_layout_position 值的当前位置时。

是否有办法在不使用 FOREACH LOOP 的情况下执行此操作？

Answer 1

在删除一行之前，您可以获取它的位置并减少具有较高位置的所有行的位置。

伪代码 ：

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();
  }
}

Answer 2

要以不间断的顺序进行更新，您可以使用以下命令：

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;

注意： @reset 应设置为 id_layout_position 的最小值，假设您在 id_layout_position 更改时重新启动计数器，如果更复杂，则需要更改 WHEN 条件，您可能会需要更多变量来保存前一行的值。
此外，IF是一个黑客，只要你将@pos设置为0而不是其他起始值，它就永远不会为NULL，我只是不知道如何强制mysql计算两个表达式（任何人，有什么优雅的东西吗？在那里？）

上面的内容已经过测试，但具有不同的列/表名称（可能会出现重新输入错误）。

编辑：我的测试不太好，上面有一个错误，而且OP似乎还需要绕过框架的安全性，所以这里有一个

使用 phpmyadmin 或 mysql 命令行执行

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 ;

（如果使用 phpMyAdmin 不要使用分隔符语句而是直接指定分隔符在界面中为//)。
当调用过程时执行正常的 SQL

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

希望这次我的测试更好。