CODEWITHSUNDEEP
mysqlsql-updateconstraints
hosseio
hosseio

Reputation: 1222

Mysql ERROR 1062 (23000): Duplicate entry. Update rows that won't violate an unique constraint

I have the following table:

 CREATE TABLE `user_favourite_posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_post_unique` (`user_id`,`post_id`))

I am trying to update all the rows with post_id in (1000, 1001) to post_id = 1005, something like the following:

update user_posts set post_id=1005 where post_id in(1000, 1001);

But I am getting the following error because of the user_post_unique constraint:

ERROR 1062 (23000): Duplicate entry 1005 for key 'user_post_unique'

Good. This is working as expected.

But I would like to update all the rows that won't violate the constraint. Is it possible in one single sentence? I can't figure out how to do it without removing some mysql flags or using temporary tables as I cannot use the same table in a subquery.

UPDATE:

An example, having the following data

+-------+---------+---------+
| id    | user_id | post_id |
+-------+---------+---------+
|  4581 |       2 | 1001    |
|  9739 |       2 | 1005    |
|  7324 |       3 | 1001    |
+-------+---------+---------+

I would like to update all the rows without problems, so the one with id 4581 remain the same

Upvotes: 2

Views: 1007

Answers (1)

Sebas
Sebas

Reputation: 21522

I think this should work:

update user_posts a
    left join user_posts b ON b.post_id IN (1000,1001,1005) AND b.user_id = a.user_id AND b.post_id > a.post_id
set a.post_id = 1005 
where 
    a.post_id in (1000, 1001)
AND b.id IS NULL
;

Upvotes: 2

Related Questions