How to bulk update mysql database without having duplicates?

user_13779917 0
Viewed 336

I have a database as follows

CREATE TABLE `user_relationships` (
  `id` int(11) NOT NULL,
  `user_id` int(20) UNSIGNED NOT NULL,
  `relationship` varchar(50) NOT NULL,
  `institution_id` int(20) UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I am having data as follows

--------------------------------------------
id | user_id | relationship | institution_id
--------------------------------------------
 1 |   12    |is_working_at |    24
--------------------------------------------  
 2 |   12    |was_working_at|    23
--------------------------------------------
 3 |   12    |was_working_at|    55
--------------------------------------------
 4 |   12    |is_trained_at |    55
--------------------------------------------

Now I have a scenario where I want to merge the institution 55 (child_id) to institution 23 (parent_id). For that I am running the following query

UPDATE user_relationships 
SET institution_id='{$parent_id}' 
WHERE (
    relationship IN ('was_working_at', 'is_working_at', 'is_trained_at') AND
    institution_id = {$child_id} 
)

This is working fine, except when there is an already existing identical relationship between the user and the institution. In that case I am getting an error like

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '12-was_working_at-23' for key 'user_id'

How can I resolve this? Is there an efficient way of writing the sql query, which will create the entry only if no previous identical relationship exists?

relational-database mysql sql
1 Answers

You can UPDATE IGNORE:

UPDATE IGNORE user_relationships 
SET institution_id='{$parent_id}' 
WHERE
    relationship IN ('was_working_at', 'is_working_at', 'is_trained_at') AND
    institution_id = {$child_id};

With the IGNORE modifier, the update statement does not abort even if errors occur during the update. Rows for which duplicate-key conflicts occur on a unique key value are not updated. Rows updated to values that would cause data conversion errors are updated to the closest valid values instead.

But after this update you must decide what should be dun with not updated rows. May be you need to remove them using:

DELETE FROM user_relationships 
WHERE
    relationship IN ('was_working_at', 'is_working_at', 'is_trained_at') AND
    institution_id = {$child_id};
user_33993560
Related