SQL Swap Primary Key Values

sql swap primary key values

Let's for the sake of simplicity assume you have two records

id   name
---------
1    john

id   name
---------
2    jim

both from table t (but they can come from different tables)

You could do

UPDATE t, t as t2
SET t.id = t2.id, t2.id = t.id
WHERE t.id = 1 AND t2.id = 2

Note:
Updating primary keys has other side effects and maybe the preferred approach would be to leave the primary keys as they are and swap the values of all the other columns.

Caveat:
The reason why the t.id = t2.id, t2.id = t.id works is because in SQL the update happens on a transaction level. The t.id is not variable and = is not assignment. You could interpret it as "set t.id to the value t2.id had before the effect of the query, set t2.id to the value t.id had before the effect of the query". However, some databases might not do proper isolation, see this question for example (however, running above query, which is probably considered multi table update, behaved according to the standard in mysql).

A better way to swap two primary key values and circumvent a ORA-00001: unique constraint violated?

You need to do it in a single statement:

UPDATE SPOKEN_LANGUAGES
   SET id = case when id = 388 then 389 else 388 end
WHERE id in (388,389);

MySQL swap primary key values

To swap id values of 1 and 2, I would use a SQL statement like this:

EDIT : this does NOT work on an InnoDB table, only works on a MyISAM table, per my testing.

UPDATE mytable a 
  JOIN mytable b ON a.id = 1 AND b.id = 2 
  JOIN mytable c ON c.id = a.id
   SET a.id = 0
     , b.id = 1
     , c.id = 2 

For this statement to work, the id value of 0 must not exist in the table, any unused value would be suitable... but to get this to work in a single SQL statement, you need to (temporarily) use a third id value.

---

This solution works for regular MyISAM tables, not temporary tables. I missed that this was being performed on a temporary table, I was confused by the error message you reported Can't reopen table:.

To swap id values 1 and 2 in a temporary table, I'd run three separate statements, again, using a temporary placeholder value of 0:

UPDATE mytable a SET a.id = 0 WHERE a.id = 1;
UPDATE mytable b SET b.id = 1 WHERE b.id = 2;
UPDATE mytable c SET c.id = 2 WHERE c.id = 0;

Edit: Fixed errors

Procedure for Interchanging Swap, Values Primary Key, Oracle, ORA-00001

Based on these Questions
SQL - How to select a row having a column with max value

Oracle SQL: Update a table with data from another table

and Answers

https://stackoverflow.com/a/42007108/811293

Check This Code:

UPDATE TABLE_REL SET POSITION_CODE_REL =
    CASE 
    WHEN POSITION_CODE_REL = 1 THEN /*MAX VALUE*/ (
        SELECT POSITION_CODE_REL FROM (SELECT * FROM TABLE_REL ORDER BY POSITION_CODE_REL DESC) WHERE rownum = 1
        )
    ELSE POSITION_CODE_REL - 1 END;

Reverse

UPDATE TABLE_REL SET POSITION_CODE_REL =
    CASE 
    WHEN POSITION_CODE_REL = /*MAX VALUE*/ (
        SELECT POSITION_CODE_REL FROM (SELECT * FROM TABLE_REL ORDER BY POSITION_CODE_REL DESC) WHERE rownum = 1
        ) THEN 1 
    ELSE POSITION_CODE_REL + 1 END;

Swap values of two rows in MySQL without violating unique constraint?

In MySql this is not easy to do, as it checks the unique constraints for every individual record during the update, not at the end (only).

So in order to allow the values of a column to be swapped, you need to allow the column to get a temporary value that will not conflict with any constraint. So if you have a column that has a foreign key constraint, and does not allow null values either, it is going to be hard to find such a value in all possible cases.

For the following solution null values must be allowed. If they currently are not, then first issue this statement to allow null for the emp_id column:

alter table testing_table modify column emp_id int null;

Then:

start transaction;
    update testing_table
       set emp_id = null
    where  id = 1 and if(@emp1 := emp_id, 1, 1)
       or  id = 2 and if(@emp2 := emp_id, 1, 1);

    update testing_table
       set emp_id = if(id = 1, @emp2, @emp1)
    where  id in (1, 2);
commit;

SQL fiddle.

Explanation

This will first set the values to null, while storing their previous values in @emp1 and @emp2, and updates the same records with the values as retained in these variables, but swapped.

The ifs in the first update statement are just there to make sure the assignment returns a true expression and make the where condition succeed. Note also that short-circuit evaluation will make sure that those assignments only happen when the left side of the and operator evaluated to true.

Swap values for two rows in the same table in SQL Server

If you want to swap values from one row to the other for two known IDs try something like this:

--need to store the original values
SELECT
    *,CASE WHEN id=123 then 987 ELSE 123 END AS JoinId
    INTO #Temp
    FROM YourTable
    WHERE ID in (123,987)

--swap values
UPDATE y
    SET col1=t.col1
        ,col2=t.col2
    FROM YourTable        y
        INNER JOIN #Temp  t ON y.id =t.JoinId
    WHERE ID in (123,987)

---

Related Topics