CODEWITHSUNDEEP
mysqlsqlselectsql-updateauto-increment
Forest
Forest

Reputation: 938

Increment a unique column in every row in one query

I'm trying to update a table which contains two columns; id and position. id is unique and automatically increments. position is unique but does not automatically increment, and in fact has to be set manually.

I need to alter all rows with a position greater than or equal to 5 (or any other number that I provide) to increment them all by one. This is my code:

UPDATE slides 
SET   position = position + 1 
WHERE position >= 5;

Unfortunately, it returns the following error:

Error : Duplicate entry '2' for key 'slides_position_unique'

How do I update all these unique number without causing conflicts as I do so? I've tried a subquery that finds all the updatable rows and returns them in reverse, but it doesn't help.

CREATE TABLE slides
  (id int(10) unsigned NOT NULL AUTO_INCREMENT,
   user_id int(10) unsigned NOT NULL,
   position int(10) unsigned NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY slides_position_unique (position),
   KEY slides_user_id_foreign (user_id),
   CONSTRAINT slides_user_id_foreign
     FOREIGN KEY (user_id) REFERENCES users (id) )

Upvotes: 0

Views: 1969

Answers (3)

gsouf
gsouf

Reputation: 1113

You can use order by in update. As mysql doc facts :

If the ORDER BY clause is specified, the rows are updated in the order that is specified

That would result in :

UPDATE slides 
SET   position = position + 1 
WHERE position >= 5
ORDER BY position DESC;

This way you update the greater first, then the greater-1, then the greater-2, etc... and the unique index is always freed before being assigned to next row.

Note that, depending on your use case, you might want to use row or table locking to avoid race conditions if other script need to access this data at the same time.

Upvotes: 7

martin
martin

Reputation: 2638

The problem is in the unique constraint -- during the update the values are not unique. You could do it in transaction or drop the constraint and then add it:

ALTER TABLE slides DROP index slides_position_unique;
UPDATE slides SET position = position + 1 WHERE position >= 1;
ALTER TABLE slides ADD CONSTRAINT  slides_position_unique UNIQUE (position);

See http://sqlfiddle.com#!9/66e0d8 for working example.

Upvotes: 3

Asenar
Asenar

Reputation: 7010

The error is pretty clear: applying this query gave duplicate rows. To avoid that change your query. I suppose that means either removing WHERE position >= 1 (to update all rows), or to add another condition to exclude slides.position < 1.

Maybe this more simple query works for you:

UPDATE slides 
SET   position = position + 1 
WHERE position >= 1 AND position IS NOT NULL

You may probably use UPDATE IGNORE slides set position = position+1 but this can make you loose some rows.

Upvotes: 0

Related Questions