SQL Update Statement to Switch Two Values in Two Rows

SQL UPDATE statement to switch two values in two rows

If 'Peter' and 'Steve' are unique in your table, this will do:

UPDATE TableX
SET ord = ( SELECT MIN(ord) + MAX(ord) 
            FROM TableX 
            WHERE name IN ('Peter', 'Steve')
          ) - ord
WHERE name IN ('Peter', 'Steve')

or (improved by @Erwin):

UPDATE TableX
SET ord = ( SELECT SUM(ord) 
            FROM TableX 
            WHERE name IN ('Peter', 'Steve')
          ) - ord
WHERE name IN ('Peter', 'Steve')

SQL UPDATE statement to switch values between multiple rows in random order

update myTable
   set Name = shuffle.Name
from myTable t
inner join 
(select id, row_number() over (order by id) as origId from mytable) orig
on orig.id = t.id
join 
(select Name, row_number() over (order by newid()) as [newId] from myTable) shuffle
on orig.OrigId = shuffle.[newId];

how to update swap values of two rows with single query

you can see the solution in this article

http://www.microshell.com/database/sql/swap-values-in-2-rows-sql/

look at the : The elegant way , make a join to get the data from the 2 rows to be swapped in 1 row, after that make an update is easy.

example :

UPDATE
rules AS rule1
JOIN rules AS rule2 ON
( rule1.rule_id = 1 AND rule2.rule_id = 4 )
SET
rule1.priority = rule2.priority,
rule2.priority = rule1.priority
;

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)

Swap two rows using sql query

use a case statement, e.g.:

update ticket
set ticket_index = case when ticket_index = :x then :y else :x end
where ticket_index in (:x, :y);

Switch values of two rows in an SQL statement

try self join

update booking as o join booking as r
set o.operator_id = r.operator_id, r.operator_id = o.operator_id
where o.id = 2 and r.id = 3;

like this

swap two column values in two different rows using mysql

I assume you have just 2 rows in your table.

If not, you need to modify slightly the JOIN conditions.

Here is one possible approach.

CREATE TEMPORARY TABLE T(ID int, Week int, Day int)
INSERT INTO T(ID, Week, Day)
SELECT ID, Week, Day from TableName;

UPDATE TableName t1
JOIN T t2 on t1.ID <> t2.ID
SET
t1.Week = t2.Week,
t1.Day = t2.Day;

DROP TEMPORARY TABLE T;

And here is a better one.

UPDATE
    tablename AS t1
    JOIN tablename AS t2 ON
           ( t1.id <> t2.id )
SET
    t1.week = t2.week,
    t2.week = t1.week,
    t1.day = t2.day,
    t2.day = t1.day;

In Oracle PL/SQL can I swap the value of the same column of two differents rows in a table when the rest of the values of the columns are equal?

You can do this with a CASE statement:

update the_table
   set the_column = case pk_column
                      when 1 then (select the_column from the_table where pk_column = 2)
                      when 2 then (select the_column from the_table where pk_column = 1)
                    end
where pk_column in (1,2);

The above assumes that pk_column is defined as unique.

How to swap values of two rows in MySQL without violating unique constraint?

Is it possible to accomplish this in MySQL without using bogus values and multiple queries?

No. (none that I can think of).

The problem is how MySQL processes updates. MySQL (in difference with other DBMS that implement UPDATE properly), processes updates in a broken manner. It enforces checking of UNIQUE (and other) constraints after every single row update and not - as it should be doing - after the whole UPDATE statement completes. That's why you don't have this issue with (most) other DBMS.

For some updates (like increasing all or some ids, id=id+1), this can be solved by using - another non-standard feature - an ORDER BY in the update.

For swapping the values from two rows, that trick can't help. You'll have to use NULL or a bogus value (that doesn't exist but is allowed in your column) and 2 or 3 statements.

You could also temporarily remove the unique constraint but I don't think that's a good idea really.

---

So, if the unique column is a signed integer and there are no negative values, you can use 2 statements wrapped up in a transaction:

START TRANSACTION ;
    UPDATE tasks 
    SET priority = 
      CASE
        WHEN priority = 2 THEN -3 
        WHEN priority = 3 THEN -2 
      END 
    WHERE priority IN (2,3) ;

    UPDATE tasks 
    SET priority = - priority
    WHERE priority IN (-2,-3) ;
COMMIT ;

---

Related Topics