CODEWITHSUNDEEP
mysql
salt
salt

Reputation: 830

Insert into change value if duplicate entry

I have this sql query:

INSERT INTO my_table
SELECT id, name, type
FROM other_table

I have this row: 1,bob,male

And I try insert: 1,bob,male

So, I have a duplicate entry error and I want change my insert value with an increment by one so after I would have two rows:

  1. bob,male <=NOT UPDATED
  2. bob,male

I don't want update the existing row, if I have a duplicate entry error. The insert increments the id value. So, I think ON DUPLICATE KEY isn't the solution.

UPDATE:

If I use a trigger like this:

DELIMITER | 
CREATE TRIGGER incremente_primary BEFORE INSERT ON my_table FOR EACH ROW 
BEGIN 
IF( EXISTS( SELECT * FROM my_table )) THEN  
SET NEW.id = NEW.id + 1; 
END IF; 
END | 
DELIMITER ;

It doesn't work because a trigger can read only one line.

Upvotes: 0

Views: 199

Answers (3)

salt
salt

Reputation: 830

I find a solution, i use a cursor:

DROP PROCEDURE proc_incremente;
DELIMITER |
CREATE PROCEDURE proc_incremente()
BEGIN

    DECLARE var_name, var_type VARCHAR(100);

    DECLARE var_id INT;

    DECLARE end TINYINT(1) DEFAULT 0;

    DECLARE cursor_incremente CURSOR
        FOR SELECT * FROM other_table;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET end = 1;

    OPEN cursor_incremente;

    loop_cursor: LOOP

        FETCH cursor_incremente INTO var_id, var_name, var_type;

        IF end = 1 THEN
            LEAVE loop_cursor;
        END IF;

        WHILE ( EXISTS( SELECT * my_table WHERE id = var_id) IS TRUE) DO
            SET var_id = var_id + 1;
        END WHILE;

        INSERT INTO my_table(id,name,type) VALUES(var_id,var_name,var_type);

    END LOOP;

    CLOSE cursor_incremente;

END |
DELIMITER ;

CALL proc_incremente();

Upvotes: 0

Zafar Malik
Zafar Malik

Reputation: 6854

As per your requirement, you need to set auto_increment property for your id and then just insert other columns except id, so that it can be auto_increment like below-

INSERT INTO my_table (name,type)
SELECT name, type
FROM other_table;

If you just want to ignore if there is duplicate then you can use-

INSERT IGNORE INTO my_table
SELECT id,name, type
FROM other_table;

Upvotes: 1

kyle
kyle

Reputation: 1

make sure that the primary key isn't the name or the type. Because you can input duplicate rows as long as you do not duplicate primary keys

Upvotes: 0

Related Questions