UPDATE table SET a='a', primary_key = next_autoincrement ?

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

I've got a simple relationship table with three columns. A category and item id columns, and a primary key auto-increment column I use to keep track of the order the relations were made.

I'll need to update these relationships occasionally when a category is deleted and the items are re-parented. A simple query that would work looks like this.

SQL Code: [ Select ]
UPDATE TABLE
SET left_id = 'new_id'
WHERE left_id = 'old_id'
  1. UPDATE TABLE
  2. SET left_id = 'new_id'
  3. WHERE left_id = 'old_id'


That will leave the old order_id column intact though and screw up the order of the new category.

Another query that would work

SQL Code: [ Select ]
INSERT INTO TABLE
SELECT 'new_id', item_id
FROM TABLE;
 
DELETE FROM TABLE WHERE category = 'old_id';
  1. INSERT INTO TABLE
  2. SELECT 'new_id', item_id
  3. FROM TABLE;
  4.  
  5. DELETE FROM TABLE WHERE category = 'old_id';


To make things more fun, I want to handle deleting multiple categories and re-parenting all of their items to the same new category at the same time. The form element for this is going to be a <select multiple/> and the re-parent a <select/>.

Surely there must be something that would let me update that order_id primary key auto-increment column to the value of the next auto-increment value on an update ?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Wouldn't a trigger be more suited to your needs here than a complex statement?
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

I actually came across this "bug" that had me a little spooked on tinkering with the AI column.

I'm looking at this relations table, the left column is a VARCHAR(64), right and order columns are INT columns. Realistically, I can probably chop that left column to 24.

I think in my worst case scenario an INSERT-SELECT to generate fresh order_id values would need 30MB of memory to store the SELECT results. And that's if there were 1M+ rows. I'd have a tough time realistically seeing more than 100K rows in this operation, which would need closer to 3MB. This is a back-end operation, it's not going to be something that happens often.

When I consider things like a checkbox that has grandparent categories adopt the items of deleted categories automatically, just dropping the existing rows and inserting fresh rows starts to seem like a better idea as too as I'll be gathering the cat_ids in chunks.

Here's the three queries I'm looking at, pre-grandparent-adoption.

PHP Code: [ Select ]
$c->categories    = default_for_in(array(), 'categories', $_POST);
$c->new_category  = default_for_in('', 'new_category', $_POST, imasks::category_id);
 
if(default_for_in(false, 'submit', $_POST))
{
   $c->sql_in  = '';
   foreach($c->categories as &$c->category)
   {
      if(preg_match(imasks::category_id, $c->category))
      {
         $c->sql_in .= ",'{$c->category}'";
      }
   }
   if($c->sql_in)
   {
      $c->sql_in  = substr($c->sql_in, 1);
      if($c->new_category)
      {
         $c->sql     = '
            INSERT IGNORE INTO ' . CAT_WP_RELATIONS_TABLE . ' (cat_id, item_id)
            SELECT "' . $db->real_escape_string($c->new_category) . '", item_id
               FROM ' . CAT_WP_RELATIONS_TABLE . "
               WHERE cat_id IN({$c->sql_in})";
         if($db->query($c->sql))
         {
            $page->messages .= new message('Added New Category/Item Relations', 'success');
         }
         else
         {
            $page->messages .= new message($db->error, 'error');
         }
      }
     
      $c->sql  = 'DELETE FROM ' . CAT_WP_RELATIONS_TABLE . " WHERE cat_id IN({$c->sql_in})";
      if($db->query($c->sql))
      {
         $page->messages .= new message('Deleted Old Category/Item Relations', 'success');
         $c->sql = 'DELETE FROM ' . CATEGORIES_TABLE . " WHERE id IN({$c->sql_in})";
         if($db->query($c->sql))
         {
            $page->messages .= new message('Deleted Categories', 'success');
         }
         else
         {
            $page->messages .= new message($db->error, 'error');
         }
      }
      else
      {
         $page->messages .= new message($db->error, 'error');
      }
      unset($c->sql, $c->sql_in);
   }
   else
   {
      $page->messages .= new message('No Categories Selected');
   }
}
  1. $c->categories    = default_for_in(array(), 'categories', $_POST);
  2. $c->new_category  = default_for_in('', 'new_category', $_POST, imasks::category_id);
  3.  
  4. if(default_for_in(false, 'submit', $_POST))
  5. {
  6.    $c->sql_in  = '';
  7.    foreach($c->categories as &$c->category)
  8.    {
  9.       if(preg_match(imasks::category_id, $c->category))
  10.       {
  11.          $c->sql_in .= ",'{$c->category}'";
  12.       }
  13.    }
  14.    if($c->sql_in)
  15.    {
  16.       $c->sql_in  = substr($c->sql_in, 1);
  17.       if($c->new_category)
  18.       {
  19.          $c->sql     = '
  20.             INSERT IGNORE INTO ' . CAT_WP_RELATIONS_TABLE . ' (cat_id, item_id)
  21.             SELECT "' . $db->real_escape_string($c->new_category) . '", item_id
  22.                FROM ' . CAT_WP_RELATIONS_TABLE . "
  23.                WHERE cat_id IN({$c->sql_in})";
  24.          if($db->query($c->sql))
  25.          {
  26.             $page->messages .= new message('Added New Category/Item Relations', 'success');
  27.          }
  28.          else
  29.          {
  30.             $page->messages .= new message($db->error, 'error');
  31.          }
  32.       }
  33.      
  34.       $c->sql  = 'DELETE FROM ' . CAT_WP_RELATIONS_TABLE . " WHERE cat_id IN({$c->sql_in})";
  35.       if($db->query($c->sql))
  36.       {
  37.          $page->messages .= new message('Deleted Old Category/Item Relations', 'success');
  38.          $c->sql = 'DELETE FROM ' . CATEGORIES_TABLE . " WHERE id IN({$c->sql_in})";
  39.          if($db->query($c->sql))
  40.          {
  41.             $page->messages .= new message('Deleted Categories', 'success');
  42.          }
  43.          else
  44.          {
  45.             $page->messages .= new message($db->error, 'error');
  46.          }
  47.       }
  48.       else
  49.       {
  50.          $page->messages .= new message($db->error, 'error');
  51.       }
  52.       unset($c->sql, $c->sql_in);
  53.    }
  54.    else
  55.    {
  56.       $page->messages .= new message('No Categories Selected');
  57.    }
  58. }

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: No registered users and 50 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.