SQL: Using a CASE Statement to update 1000 rows at once
- by SoLoGHoST
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 from 0 - count(position) for each id_layout_position column per id_layout column.
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. But I want to add something at the end now and make sure that it has the last possible position, but the positions are already messed up, so I need to reorder them like so before I insert the new row: 0, 1, 2, 3, 4, 5, 6. But it must be ordered by lowest first. So 0 stays at 0, 1 stays at 1, 3 gets changed to 2, the 4 at the end gets changed to a 3, 5 gets changed to 4, 6 gets changed to 5, and 7 gets changed to 6. Hopefully you guys get the picture now. I'm completely lost here. Also, note, this table is tiny compared to how fast it can grow in size, so it needs to be able to do this FAST, thus I was thinking on the CASE STATEMENT for an UPDATE QUERY.
Here's what I got for a regular update, but I don't wanna throw this into a foreach loop, as it would take forever to do it. I'm using SMF (Simple Machines Forums), so it might look a little different, but the idea is the same, and CASE statements are supported...
$smcFunc['db_query']('', '
UPDATE {db_prefix}dp_positions
SET
position = {int:position}
WHERE id_layout_position = {int:id_layout_position} AND id_layout = {int:id_layout}',
array(
'position' => $position++,
'id_layout_position' => (int) $id_layout_position,
'id_layout' => (int) $id_layout,
)
);
Anyways, I need to apply some sort of CASE on this so that I can auto-increment by 1 all values that it finds and update to the next possible value. I know I'm doing this wrong, even in this QUERY. But I'm totally lost when it comes to CASES. Here's an example of a CASE being used within SMF, so you can see this and hopefully relate:
$conditions = '';
foreach ($postgroups as $id => $min_posts)
{
$conditions .= '
WHEN posts >= ' . $min_posts . (!empty($lastMin) ? ' AND posts <= ' . $lastMin : '') . ' THEN ' . $id;
$lastMin = $min_posts;
}
// A big fat CASE WHEN... END is faster than a zillion UPDATE's ;).
$smcFunc['db_query']('', '
UPDATE {db_prefix}members
SET id_post_group = CASE ' . $conditions . '
ELSE 0
END' . ($parameter1 != null ? '
WHERE ' . (is_array($parameter1) ? 'id_member IN ({array_int:members})' : 'id_member = {int:members}') : ''),
array(
'members' => $parameter1,
)
);
Before I do the update, I actually have a SELECT which throws everything I need into arrays like so:
$disabled_sections = array();
$positions = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
{
if (!isset($disabled_sections[$row['id_group']][$row['id_layout']]))
$disabled_sections[$row['id_group']][$row['id_layout']] = array(
'info' => $module_info[$name],
'id_layout_position' => $row['id_layout_position']
);
// Increment the positions...
if (!is_null($row['position']))
{
if (!isset($positions[$row['id_layout']][$row['id_layout_position']]))
$positions[$row['id_layout']][$row['id_layout_position']] = 1;
else
$positions[$row['id_layout']][$row['id_layout_position']]++;
}
else
$positions[$row['id_layout']][$row['id_layout_position']] = 0;
}
Thanks, I know if anyone can help me here it's definitely you guys and gals...
Anyways, here is my question:
How do I use a CASE statement in the first code example, so that I can update all of the rows in the position column from 0 - total # of rows found, that have that id_layout value and that id_layout_position value, and continue this for all different id_layout values in that table?
Can I use the arrays above somehow? I'm sure I'll have to use the id_layout and id_layout_position values for this right? But how can I do this?
Ok, guy, I get an error, saying "Hacking Attempt" with the following code:
// Updating all positions in here.
$smcFunc['db_query']('', '
SET @pos = 0;
UPDATE {db_prefix}dp_positions
SET
position=@pos:=@pos+1
ORDER BY id_layout_position, position',
array(
)
);
Am I doing something wrong? Perhaps SMF has safeguards against this approach?? Perhaps I need to use a CASE STATEMENT instead?
