hi. i have a table products with 2 columns productid,position productid,position are an unique index my problem is that i need to change the position between 2 rows. for example id | productid | position 1 13 | 5 2 13 | 6 3 13 | 7 i need to change position 5 with position 6 the new table should look like this: id | productid | position 1 13 | 6 2 13 | 5 3 13 | 7 I think it's very easy. can anyone help me? Thanks
You will need to set the value of one of them to null or a higher value and then back down to prevent an error when creating a duplicate. SET AUTOCOMMIT = 0; START TRANSACTION; UPDATE product_table SET position = NULL WHERE id = 1; UPDATE product_table SET position = 5 WHERE id = 2; UPDATE product_table SET position = 6 WHERE id = 1; COMMIT; If you're using a web application ie: php, you can run this as a transaction as well to prevent data on one row from getting changed without it getting changed on the other. Also if your table doesn't allow NULL, I would do something like this: SET AUTOCOMMIT = 0; START TRANSACTION; UPDATE product_table SET position = ((SELECT MAX(position) FROM product_table) + 1) WHERE id = 1; UPDATE product_table SET position = 5 WHERE id = 2; UPDATE product_table SET position = 6 WHERE id = 1; COMMIT; You don't have to use a transaction here, but it will help prevent data from getting messed up. Either all of the UPDATE's work or none of them work this way.