Suresh Dasari
Editor
2 min read | 4 months ago

Swap Column Values in MySQL table

We got one scenario in our live project, there we have lot of data in one MySql table, in which two columns A and B data wrongly inserted,  A column data inserted in B and B column data inserted in A. So we need to swap those values to make it correct.  We used the following sql command to accomplish the task.

UPDATE table_test t1, table_test t2 SET t1.col_a=t1.col_b, t1.col_b=t2.col_a WHERE t1.id=t2.id;

Note : Mysql table should have PRIMARY KEY

Test schema and data to test :

CREATE TABLE `table_test` (
  `id` int(11) NOT NULL,
  `col_a` varchar(55) DEFAULT NULL,
  `col_b` varchar(55) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `table_test`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `table_test`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
INSERT INTO `table_test` VALUES (1, 'eeee', '10');
INSERT INTO `table_test` VALUES (2, NULL, '20');
INSERT INTO `table_test` VALUES (3, 'ffff', NULL);
INSERT INTO `table_test` VALUES (4, 'gggg', '40');


Warning! This site uses cookies
By continuing to browse the site, you are agreeing to our use of cookies. Read our terms and privacy policy