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

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.



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

Another query that would work



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 ?

Wouldn't a trigger be more suited to your needs here than a complex statement?

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.