CODEWITHSUNDEEP
mysqlsql
Panida Yimsanga
Panida Yimsanga

Reputation: 23

How to update in table with unique key

table

CREATE table REWARD (  
    HID bigint(15) UNSIGNED zerofill,
    ITEM_ID int  UNIQUE ,
    TYPE varchar(50) NOT NULL,
    IS_USED int ,
    DATE_SERV date ,
    FOREIGN key (ITEM_ID) REFERENCES CODE_REWARD (ITEM_ID),
    primary key (HID,ITEM_ID),
    unique (HID,TYPE,IS_USED)
) ;

SQL QUERY

UPDATE REWARD
    SET IS_USED = CASE ITEM_ID
    WHEN 2  THEN NULL
    WHEN 1  THEN 1
    END
WHERE HID = 1 
AND ITEM_ID in(1,2)

error

FAIL: UPDATE REWARD SET IS_USED = CASE ITEM_ID WHEN 2 THEN NULL WHEN 1 THEN 1 END WHERE HID = 1 AND ITEM_ID in (1,2)
Message :
Duplicate entry '000000000000001-background-1' for key 'HID

data

HID ITEM_ID TYPE IS_USED DATE_SERV
1 1 background NULL 2022-01-01
1 2 background 1 2022-01-01

expect

HID ITEM_ID TYPE IS_USED DATE_SERV
1 1 background 1 2022-01-01
1 2 background NULL 2022-01-01

Can I update in 1 query with this constraint

Upvotes: 0

Views: 973

Answers (1)

Andrew
Andrew

Reputation: 8758

You have to do this in multiple queries. MySQL checks unique constraints after each row is updated. Either of the individual updates in your example will cause a duplicate.

Or you could temporarily remove the constraint.

Upvotes: 1

Related Questions