MySQL: How to update unique key on duplicate unique key

Question

Given the following table structure, how can I change the value of primary to 0 when a duplicate unique index is found?

CREATE TABLE `ncur` (
    `user_id` INT NOT NULL,
    `rank_id` INT NOT NULL,
    `primary` TINYINT DEFAULT NULL,
    PRIMARY KEY (`user_id`, `rank_id`),
    UNIQUE (`user_id`, `primary`)
);

So, when I run a query like this:

UPDATE `ncur` SET `primary` = 1 WHERE `user_id` = 4 AND `rank_id` = 5;

When a constraint of user_id-primary is matched, I want it to set all primary values for user_id to NULL, and then complete the update query by updating the row it had found.

A bit off topic but why have a unique constraint on user_id and a primary key on user_id and rank_id? — Namphibian, Dec 24 '13 at 02:02
Because each `user_id` can have many `rank_id`'s (`PRIMARY`) and they are only allowed to have one `primary` (not null). — Nahydrin, Dec 24 '13 at 02:04
You are trying to update multiple rows in a statement that intends to only write/update one row. You will either need to handle this in your application, or alternatively, write an INSERT ... ON DUPLICATE KEY statement for all rows with on user_id and use logic in the ON DUPLICATE KEY clause. — user2923779, Dec 24 '13 at 02:14
"I want it to set all primary values for user_id to 0.." Assuming this is done for a specific `user_id` the first time, the second time it will not be allowed as it will otherwise violate the UNIQUE constraint condition. — RGO, Dec 24 '13 at 02:16
The unique index means that two rows for a given `user_id` cannot have the same `primary` value -- setting multiple rows to the same combination of values just isn't allowed. It sounds like you want a filtered index/constraint, which MySQL does not directly support, although you can implement it using a trigger. — Gordon Linoff, Dec 24 '13 at 02:17
@RezaGoodarzi When it violates the unique constraint, I want it to change the values of `primary` of the specified `user_id` back to `NULL`. Then proceed with the update. @GordonLinoff, do you know a trigger that can accomplish this? — Nahydrin, Dec 24 '13 at 02:20
Ok saw my mistake. Too close to xmas to think properly makes sense. — Namphibian, Dec 24 '13 at 02:52
http://stackoverflow.com/questions/16377932/mysql-behavior-of-on-duplicate-key-update-for-multiple-unique-fields — , Dec 24 '13 at 03:20
@AvinashBabu That pertains to duplicate keys when inserting, my query needs to update the duplicate indexes that are found before updating my query. Please, actually read my question. — Nahydrin, Dec 24 '13 at 03:33

RGO · Answer 1 · 2013-12-24T05:04:28.880

I am not as much familiar with MySQL as I am with Oracle; However, I think this query should work for you:

UPDATE `ncur` a
SET `primary` = (

    /* 1st Subquery */
    SELECT 1 FROM (SELECT * FROM `ncur`) b        
    WHERE b.`user_id` = a.`user_id` AND b.`rank_id` = a.`rank_id` 
                                                           AND a.`rank_id` = 5

    UNION ALL 

    /* 2nd Subquery */ 
    SELECT 0 FROM (SELECT * FROM `ncur`) b
    WHERE b.`user_id` = a.`user_id` AND b.`rank_id` <> 5 AND a.`rank_id` <> 5
    GROUP BY `user_id`
    HAVING COUNT(*) = 1
) 
WHERE `user_id` = 4

Justification:

The query updates all the records that have user_id = 4.

For each of such records, primary is set to a different value of 1, 0, or NULL, depending on the value of rank_id in this record as well as the information regarding how many other records with the same user_id exists in the table.

The subquery that returns the value for primary consists of three subqueries, only one of which returns a value depending on the circumstances.

1st Subquery: This subquery returns 1 for the record with rank_id = 5; Otherwise it returns NULL.
2nd Subquery: This subquery returns 0 for the records with rank_id != 5 if there is only one such record in the table; otherwise it returns NULL.

Please note: if the query is run while there are no records with rank_id = 5, it will still update the other records according to the rules specified above. If this is not desired, the condition in the parent query must be changed from:

WHERE `user_id` = 4

to:

WHERE `user_id` = 4 AND 
      EXISTS(SELECT * FROM (SELECT * FROM `ncur`) b WHERE 'rank_id` = 5)

MySQL: How to update unique key on duplicate unique key

1 Answers1