0

In a table i have unique value check for the combination of box_number,price,start_val,end_val

so there can not be a two box number with same number which have same price , start_val and end_val..

table data right now

| box_type_id | box_number | price |start_val | end_val
|  1          |   7        | 5.00  |100       |1
|  2          |   5        | 10.00 |50        |40
|  3          |   5        | 5.00  |100       |1

what i want to do i want to change box number 7 with 5 for box_type_id 1 and change box number 5 with 7 for box type id 3 ; my query is below... giving me error

MySQL Database Error: Duplicate entry '7-5.00-100-1' for key 'unique_index'

but here i am setting unique key check to 0. before executing query.

My query

SET UNIQUE_CHECKS=0;
 Update `box_types` `bt` SET `bt`.`box_number` = "7" where `bt`.`box_type_id` = "3" ;
 Update `box_types` `bt` SET `bt`.`box_number` = "5" where `bt`.`box_type_id` = "1" ;
SET UNIQUE_CHECKS=1;

how do i solve this problem.. is there any other prblem ? please guide me..

2
  • this will create a huge problem for you, since there is a validation or constraint on the database table and you disable it (as a work around) to add, update, or delete any value you need, after that enable it again, what I suggest is to remove this index since you don't need it. Feb 16, 2015 at 10:26
  • actually i need it while inserting ... but i want to swap data between two previously inserted ... so there i got the problem.. any solution for that Feb 16, 2015 at 11:14

1 Answer 1

2

Swapping is a 3-step process (for this and many other situations). Go via a temp id. Something like:

Update `box_types` `bt` SET `bt`.`box_number` = "-1" where `bt`.`box_type_id` = "3" ;
Update `box_types` `bt` SET `bt`.`box_number` = "5" where `bt`.`box_type_id` = "1" ;
Update `box_types` `bt` SET `bt`.`box_number` = "7" where `bt`.`box_number` = "-1" ;

(If box_number is UNSIGNED, then pick some value that will work: maybe 0? Maybe 9999999?)

1
  • 1
    But is there any other better way...!!! MySql way...!!! Feb 17, 2015 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.