How to insert on MySQL when no row affected on update in one request?-CodePudding

this is my request for update :

UPDATE my_table 
SET column_i_want_to_update = 'colummn value' 
WHERE id_which_is_not_primary_key_nor_unique = 'id value'

If 0 row is affected with this update, i want to run my insert request :

INSERT INTO my_table (column_i_want_to_update, id_which_is_not_primary_key_nor_unique) 
VALUES ('colummn value', 'id value')

But i want to perform that in one request instead of two. I've attempted this request :

INSERT INTO my_table (id_which_is_not_primary_key_nor_unique, column_i_want_to_update) 
VALUES ('id value', 'colummn value') 
ON DUPLICATE KEY 
UPDATE column_i_want_to_update = column_i_want_to_update

but this doesn't work because my id is not a primary key nor unique. Is there a solution for this? Thanks in advance.

CodePudding user response：

Add a unique index over the two columns.

ALTER TABLE `my_table` 
ADD UNIQUE `my_unique_index`
(id_which_is_not_primary_key_nor_unique, column_i_want_to_update);

Now the ON DUPLICATE KEY UPDATE ... should work.

CodePudding user response：

You could use and if Expression

IF(expression ,expr_true, expr_false);

So under the expression use the

SELECT IF(( mysql_affected_rows(&mysql) >0 THEN 'true' ELSE 'false' 
END),'true','false');

or something similar

IF condition THEN
   statements;
ELSE
   else-statements;
END IF;