SQL: Using a CASE Statement to update a 1000 rows at once, how??
- 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.
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?
