SQL: Using a CASE Statement to update 1000 rows at once
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: alt text http://acs.graphicsmayhem.com/images/dp_positions_table.png
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_positio开发者_运维问答n' => $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
?
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();
}
}
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.
精彩评论