CODEWITHSUNDEEP
mysqlsql-updatemariadb
sg552
sg552

Reputation: 1543

Allowed duplicate record if data not exist in different column

I have 2 column name location_id and district_id. location_id record can be duplicated if existing location_id and district_id record does not match. The table below will explain it.

enter image description here

Notice the duplicate record of location_id and district_id. This table has valid data.

How can I UPDATE with mysql while preventing duplicate. With below syntax, my table can be updated even if B008 and 01010244 already exist.

    UPDATE 
    `location_id`, district_id 
    SET
      `location_id` = 'B008',
      `district_id ` = 0101031504
    WHERE `id` = 5

My second query above will update even though existing record with similar data already existed.

Please assist and thanks in advance.

Upvotes: 2

Views: 35

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can solve this problem structurally, by adding a Composite Unique constraint on the location_id and district_id together. This will block any new DML operations trying to create duplicate rows for the combination.

ALTER TABLE your_table_name 
ADD UNIQUE `unique_location_district` (`location_id`, `district_id`)

Upvotes: 2

Related Questions