CODEWITHSUNDEEP
mysql
Nahydrin
Nahydrin

Reputation: 13507

MySQL: How to update unique key on duplicate unique key

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.

Upvotes: 2

Views: 1402

Answers (1)

RGO
RGO

Reputation: 4727

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)

Upvotes: 1

Related Questions