I have 2 column name
location_id record can be duplicated if existing
district_id record does not match. The table below will explain it.
Notice the duplicate record of
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
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.
You can solve this problem structurally, by adding a Composite
Unique constraint on the
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`)