Updated: June 9th, 2009
Today I had to swap 2 columns in one of my MySQL tables. The task, which seems easily accomplishable by a temp variable, proved to be a bit harder to complete. But only just a bit.
Here are my findings:
-
The
UPDATE swap_test SET x=y, y=x;
approach doesn't work, as it'll just set both values to y.
PostgreSQL seems to handle this query differently, as it apparently uses the old values throughout the whole query. [Reference] -
Here's a method that uses a temporary variable. Thanks to Antony from the comments for the "IS NOT NULL" tweak. Without it, the query works unpredictably. See the table schema at the end of the post. This method doesn't swap the values if one of them is NULL. Use method #3 that doesn't have this limitation.
UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;
The parentheses around @temp:=x are critical. Omitting them will cause data corruption.1 2 3
mysql> UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL; Query OK, 3 rows affected Rows matched: 3 Changed: 3 Warnings: 0
-
This method was offered by Dipin in the comments. I think it’s the most elegant and clean solution. It works with both NULL and non-NULL values.
UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;
-
Another approach I came up with that seems to work:
UPDATE swaptest s1, swaptest s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;
1 2 3
mysql> update swap_test s1, swap_test s2 set s1.x=s1.y, s1.y=s2.x where s1.id=s2.id; Query OK, 3 rows affected Rows matched: 3 Changed: 3 Warnings: 0
Essentially, the 1st table is the one getting updated and the 2nd one is used to pull the old data from.
Test schema used:
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE `swap_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`x` varchar(255) DEFAULT NULL,
`y` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `swap_test` VALUES ('1', 'a', '10');
INSERT INTO `swap_test` VALUES ('2', NULL, '20');
INSERT INTO `swap_test` VALUES ('3', 'c', NULL); |
Do you have a better approach? If so, please share in the comments.
Some references:
- http://stackoverflow.com/questions/37649/swapping-column-values-in-mysql/ – some discussion on various methods, which eventually prompted me to start this post
- http://www.marcworrell.com/article-3026-en.html – discussion on the 2nd approach, which doesn’t work
Artem Russakovskii is a San Francisco programmer, blogger, and future millionaire (that last part is in the works). Follow Artem on Twitter (@ArtemR) or subscribe to the RSS feed.
In the meantime, if you found this article useful, feel free to buy me a cup of coffee below.
beer planet is a blog about technology, programming, computers, and geek life. It is run by Artem Russakovskii - a local San Francisco geek who currently works at 
Artem loves commenting on other people's blogs and reading comments on his own, so go ahead and leave one.
I can haz performance metrics plz>?
@Ikai
The query doesn't use a temp table or filesort, which are 2 of the biggest performance hogs.
It performed really well on about 70k items that I ran it on and the only performance hit is the usual join penalty and larger memory footprint.
However, it does the job well, and I'm willing to accept such a penalty until another working solution appears.
Hi,
This appears to work fine for me:
UPDATE swap_test SET x = y, y = @temp WHERE (@temp := x) IS NOT NULL;Regards,
@Antony
Verified as working. Thank you. The IS NOT NULL tweak does the job, though I'm not 100% sure why.
The IS NOT NULL is a complex operator which the optimizer doesn't strip out and replace with a constant.
As an aside…
A better alternative would be to rename the columns to swap them. If the table definition is not changed by the operation, then it will be fast and efficient.
Unfortunately, MySQL's fallback method when it can't simply rename the columns involves a CREATE TEMP / INSERT .. SELECT / DROP / RENAME behind the scenes which would be worse than iterating through all the rows with an UPDATE operation.
From the MySQL manual:
YMMV.
Hmm, generally this kind of situation occurs with a flawed schema design?
I just noticed one horrible, subtle thing:
vs
In the first query, the missing parentheses will cause data corruption. I will make sure to point it out and correct my example.
@Arjen
What kind of situation and what is the flaw in the schema? Can you provide more details please?
@Antony
Thanks for all the hints.
I definitely didn't want to go the table rename approach, as MySQL is indeed quite dumb at making the slightest changes to the schema (even the .frm hack could be adopted by the server natively, why in the world hasn't that happened yet?).
In my particular case, I only wanted to modify a very small subset of the data.
Hi!
"In the first query, the missing parentheses will cause data corruption. I will make sure to point it out and correct my example."
I think it's a matter of operator precedence. Look here: http://dev.mysql.com/doc/refman/5.1/en/operator-precedence.html
As you can see, the := binds weakest so omitting the parenthesis will cause
x IS NOT NULL
to be evaluated first. That expression evaluates to a boolean value, but MySQL doesn't have those so it becomes either 1 (TRUE) or 0 (FALSE)
As for the obvious first query,
UPDATE swap_test SET x=y, y=x;
do you notice any difference between InnoDB and MyISAM? Personally I think this query should just work but I am aware of the fact that MySQL takes a few shortcuts in the UPDATE implementation.
@Roland
Yeah, that makes sense, and I thought the caveat was worth pointing out, seeing how some examples on the web are omitting parentheses.
As far as the first query, I just tested both MyISAM and InnoDB and they both behave the same – x and y end up equal, as expected. The MySQL manual confirms it: "Single-table UPDATE assignments are generally evaluated from left to right."
All major databases "think is sets" and make the swap seamless, if I remember well just MySQL and Firebird don't work this way.
I would not recommend to use this approach, as the update fails to swap values if one of them is null:
drop table if exists swap_test_1
;
create table swap_test_1
(
id int not null auto_increment
, x varchar(255) null
, y varchar(255) null
, primary key (id)
) engine=innodb;
insert into swap_test_1 values
('1', 'a', '10')
, ('2', 'b', null)
, ('3', 'c', '30')
;
update swap_test_1 set x=y, y=@temp where (@temp:=x) is not null;
select * from swap_test_1;
*** use this record set:
insert into swap_test_1 values
('1', 'a', '10')
, ('2', null, '20')
, ('3', 'c', '30')
;
@Dmitri
You're absolutely right. I had it in the back of my mind to test the NULL case but it escaped me till now. So, approach #2 doesn't swap the values if one of them is NULL. Approach #3 still works fine. I'll make a note of it, until a new approach is developed for #2.
update swap_test_1 set x=(@temp:=x), x = y, y = @temp;
Works for all scenarios based on my quick tests.
@Dipin
Brilliant. That indeed works for all test cases. I think this is finally the cleanest and most elegant solution.
We're aware of the problem.
http://forge.mysql.com/worklog/task.php?id=927
Thanks for that helpful post. that was awesome
I shall necessarily try
maybe you can do something with this aswell
had some problems with switching integer values in 2 different rows with eachother.
they are used to describe an order, and eacht time there will be a difference of one.
this is what did the trick.
UPDATE tablename AS first, tablename AS second, tablename AS third
SET first.ord = second.ord, second.ord = third.ord
WHERE first.parent = second.parent AND first.id = 1 AND first.id = third.id AND second.ord = third.ord-1 AND first.status > 0 AND second.status > 0
I've used
('1', 'a', '10')
, ('2', null, '20')
, ('3', 'c', '30')
It's great !
Hi,
i looked around a lot but do not seem to get it right:
I'd like to swap two values in different lines. As entry id 14 (e.g) has "order" 5 and another has "order" 6.
Is there any way to swap these two within one single query? I already have the id (14) and order of the first one.
I ran through different approaches like "ORDER BY order DESC LIMIT 1" but it always takes two queries…
Best Regards,
Dogo
@Dogo
Try posting this at http://www.stackoverflow.com. Should have a discussion going in no titme.
@Dogo: Swapping values between 2 rows from the same table is explained on my post here: http://www.microshell.com/database/sql/swap-values-in-2-rows-sql/
In a sense, it's basically the same solution as this post. You do self join on the table, then swap columns.
What about swapping the id column of table 1, with another column from table 2?
Can I apply any of these methods to swap columns across tables?
[...] Swapping Column Values in MySQL [...]
[...] Swapping Column Values in MySQL [...]
[...] On beer planet, Artem Russakovskii publishes his findings on swapping column values in MySQL. [...]
[...] Swapping Column Values in MySQL [...]