CODEWITHSUNDEEP
mysql
nebkat
nebkat

Reputation: 8565

Auto Increment If Duplicate Column in MySQL

I have a table of items with primary column id AUTO_INCREMENT. Each item has a type (for the sake of example, types can be apple orange or banana) and some column data. How can I make a column iteration, which would on insertion of a new item be the incrementing value/id for that specific type of item?

Also since the table is already filled with data, how can I update the existing data to follow that pattern, in order of time_added.

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `iteration` int(11) NOT NULL,
  `type` smallint(6) NOT NULL,
  `data` binary(16) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_iteration` (`type`,`data`) USING BTREE;

Upvotes: 0

Views: 714

Answers (2)

MikeVe
MikeVe

Reputation: 1120

Here is a very basic solution, but it actually should do what you requested:

INSERT INTO items
(SELECT null,CASE WHEN 'banana' NOT IN(SELECT type FROM items) THEN MAX(iteration) + 1 ELSE (SELECT increment FROM items WHERE type='banana') END,other_values,other_values 
FROM items)

The first value in SELECT needs to be null since you want it to remain as auto_increment. The Second value evaluates whether your new type (banana in this case) is NOT IN your types-columns. If so it adds 1 to your max iteration value ELSE it sets the value which is already associated with banana

So if you add a type banana and there is no banana in it, It should increment the max vaule of increment. If there is already a banana in your table it should give the banana the increment vaule which is associated with banana.

EDIT: Sorry, forgot about the second part of your request where you want to update your data based on time_added:

 BEGIN
        DECLARE col_id INT DEFAULT 1;
        DECLARE stepLoopDone BOOLEAN DEFAULT FALSE;
        DECLARE counter INT DEFAULT 1;
        DECLARE col_type SMALLINT(6) DEFAULT 1;

        DECLARE ORDER_CURSOR CURSOR FOR

            SELECT id, type
            FROM items
            ORDER BY time_added ASC;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET stepLoopDone = TRUE;
        OPEN ORDER_CURSOR;

        myLoop: LOOP
            FETCH ORDER_CURSOR INTO col_id, col_type;

            IF stepLoopDone THEN
                LEAVE myLoop;
            END IF;

            /*YOUR UPDATE COMMAND*/
            UPDATE items
            SET increment = counter
            WHERE id = col_id
            AND type = col_type
            /*YOUR UPDATE COMMAND*/

            SET counter = counter + 1;

        END LOOP;

        CLOSE ORDER_CURSOR;
END

Use this second script with caution. Since i can't test it with your environment i'm not 100% sure what is does to your data. Maybe you should do a backup and test it then. I made a small test-table on my machine and it worked.

Upvotes: 0

Alex
Alex

Reputation: 17289

You need to set a trigger function against your items table:

DELIMITER //
CREATE TRIGGER item_insert BEFORE INSERT ON items
FOR EACH ROW
BEGIN
IF (NEW.iteration IS NULL OR NEW.iteration=0) THEN
SET NEW.iteration = (SELECT 1+MAX(iteration) FROM items WHERE type=NEW.type);
END IF;
END; //
DELIMITER ;

Upvotes: 1

Related Questions