5 Replies - 283 Views - Last Post: 05 April 2017 - 07:11 AM

#1 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

Switching two values in a unique constraint

Posted 05 April 2017 - 06:10 AM

Hello,

I've got the following setup (well, the part I have a problem with):
CREATE TABLE test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    foo INT NOT NULL,
    bar INT NOT NULL,
    UNIQUE KEY (foo, bar)
)



Now I want to switch the bar values for all rows with two given values. If I try to do that in separate UPDATE queries, the unique constraint will prevent that.

Example: swapping bar=1 with bar=2
+----+-----+-----+
| id | foo | bar |
+----+-----+-----+
| 11 |  1  |  1  |
| 12 |  2  |  1  |
| 13 |  1  |  2  |
+----+-----+-----+


should become
+----+-----+-----+
| id | foo | bar |
+----+-----+-----+
| 11 |  1  |  2  |
| 12 |  2  |  2  |
| 13 |  1  |  1  |
+----+-----+-----+


but I have no idea how to get around the constraint. I initially thought that a joined table update could do that, but I couldn't come up with a suitable join condition.

Is This A Good Question/Topic? 0
  • +

Replies To: Switching two values in a unique constraint

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

Re: Switching two values in a unique constraint

Posted 05 April 2017 - 06:32 AM

View PostDormilich, on 05 April 2017 - 03:10 PM, said:

I initially thought that a joined table update could do that, but I couldn't come up with a suitable join condition.

Even after managing a suitable join, the constraint would still prevent the update.
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13489
  • View blog
  • Posts: 53,885
  • Joined: 12-June 08

Re: Switching two values in a unique constraint

Posted 05 April 2017 - 06:59 AM

My thought would be:
- write all the data out to a temp table,
- flip the column values on the temp table,
- Apply keys to the temp table to make sure it's all legit,
- truncate the data on the original table,
- and insert the data back from the temp table.

A little involved, but it works.. or remove the key constraint, do the update, and reapply the constraint.
Was This Post Helpful? 2
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

Re: Switching two values in a unique constraint

Posted 05 April 2017 - 07:01 AM

I'll probably go for the constraint thing, but I will try how it works out.
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13489
  • View blog
  • Posts: 53,885
  • Joined: 12-June 08

Re: Switching two values in a unique constraint

Posted 05 April 2017 - 07:03 AM

Haha.. good luck. I would really suggest testing it all out on a temp table first to make sure nothing sneaks up on you and bites you on the ass. Totally happened to "a friend".
Was This Post Helpful? 1
  • +
  • -

#6 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

Re: Switching two values in a unique constraint

Posted 05 April 2017 - 07:11 AM

I've got a vagrant machine for that. If anything fails, I re-initialise the whole DB.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1