phpmyadmin Question posted in
The official documentation can be found here.

'Insert if not exists' – Phpmyadmin

Is there any way I can insert into, if values not exists in any row?
Let’s say I have a table:

id(auto_increment)|id_user|id_translation
------------------------------------------
39                |5      | 20
-

Tried

INSERT IGNORE INTO `usertranslation`(`id_translation`, `id_user`) VALUES ('20','5')
WHERE not EXISTS (
    SELECT * from `usertranslation`
    WHERE `id_translation` = '20' AND
    `id_user` = '5' )

And similar with replace, but nothing works for me

Tags:

2

Answers


  1. 0

    For prevent duplicates in table the UNIQUE constraints must be used.
    So first you need to change your table like:

    ALTER TABLE `usertranslation`
ADD CONSTRAINT `unique_user_transaction` UNIQUE KEY(`id_translation`, `id_user`);

    After table changed you can use:

    INSERT IGNORE INTO `usertranslation`(`id_translation`, `id_user`) VALUES ('20','5');

    Pay attention INSERT IGNORE prevent error on attempt to insert duplicates, but increase auto increment field as well.

    Login or Signup to reply.
  2. 0

    Either use:

    INSERT IGNORE INTO `usertranslation`(`id_translation`, `id_user`) VALUES ('20','5')

    which will not insert the new row (without an error) if there are existing constraints which are conflicting with the new row,
    or INSERT with SELECT and not VALUES:

    INSERT INTO `usertranslation`(`id_translation`, `id_user`) 
SELECT '20', '5'
WHERE NOT EXISTS (    
  SELECT 1 from `usertranslation`
  WHERE `id_translation` = '20' AND `id_user` = '5' 
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search