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 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_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?
Answer
Solution:
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 :
Answer
Solution:
To update with uninterrupted sequence you can use the following:
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
(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
Hope my testing was better this time.