Change existing row ID based on AUTO_INCREMENT (unique key)

Question

I have a table that records tickets that are separated by a column that denotes the "database". I have a unique key on the database and cid columns so that it increments each database uniquely (cid has the AUTO_INCREMENT attribute to accomplish this). I increment id manually since I cannot make two AUTO_INCREMENT columns (and I'd rather the AUTO_INCREMENT take care of the more complicated task of the uniqueness).

This makes my data look like this basically:

-----------------------------
|  id  |  cid  |  database  |
-----------------------------
|    1 |    1  |          1 |
|    2 |    1  |          2 |
|    3 |    2  |          2 |
-----------------------------

This works perfectly well.

I am trying to make a feature that will allow a ticket to be "moved" to another database; frequently a user may enter the ticket in the wrong database. Instead of having to close the ticket and completely create a new one (copy/pasting all the data over), I'd like to make it easier for the user of course.

I want to be able to change the database and cid fields uniquely without having to tamper with the id field. I want to do an UPDATE (or the like) since there are foreign key constraints on other tables the link to the id field; this is why I don't simply do a REPLACE or DELETE then INSERT, as I don't want it to delete all of the other table data and then have to recreate it (log entries, transactions, appointments, etc.).

How can I get the next unique AUTO_INCREMENT value (based on the new database value), then use that to update the desired row?

For example, in the above dataset, I want to change the first record to go to "database #2". Whatever query I make needs to make the data change to this:

-----------------------------
|  id  |  cid  |  database  |
-----------------------------
|    1 |    3  |          2 |
|    2 |    1  |          2 |
|    3 |    2  |          2 |
-----------------------------

I'm not sure if the AUTO_INCREMENT needs to be incremented, as my understanding is that the unique key makes it just calculate the next appropriate value on the fly.

It all sounds a bit unclear to me. Also, in your first example your say `cid` has AUTO_INCREMENT, but it hasnt `1,1,2`. — putvande, Nov 12 '13 at 18:18
It does, it increments based on the `database` field. I set it up similar to this question: http://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with-auto-increment Note my comment about it working on InnoDB (which is the scheme of my table). — Demonslay335, Nov 12 '13 at 18:23
I didn't even know AUTO_INCREMENT could work like that. Cool. Like you said: you can have only 1 of those. Why have a single column PK if `cid + database` is unique? Make that your PK. (Maybe that's not the issue at all. Correct me.) — Rudie, Nov 12 '13 at 20:54
Ya, the issue wasn't so much the type of key itself, as I do have a unique key on that pair. My issue was how to invoke the mechanism of `AUTO_INCREMENT` without inserting. See my answer below. I will re-evaluate my table structure to see if making `cid + database` the PK would make sense for other implications, thanks for the suggestion. — Demonslay335, Nov 12 '13 at 21:58

score 0 · Answer 1 · edited May 23 '17 at 10:25

I actually ended up making it work once I re-read an except on using AUTO_INCREMENT on multiple columns.

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

This was the clue I needed. I simply mimic'd the query MySQL runs internally according to that quote, and joined it into my UPDATE query as such. Assume $new_database is the database to move to, and $id is the current ticket id.

UPDATE `tickets` AS t1,
(
    SELECT MAX(cid) + 1 AS new_cid
    FROM `tickets`
    WHERE database = {$new_database}
) AS t2
SET t1.cid = t2.new_cid,
    t1.database = {$new_database}
WHERE t1.id = {$id}

Change existing row ID based on AUTO_INCREMENT (unique key)

1 Answers1