0

User has entered data in wrong columns. For example, I have a table with two columns applicant name and father name. Data operator has entered father name in applicant name column and applicant name in father name column. Please suggest a way to swap the data in both columns i.e data in applicant name column should move to father name column and data in father name column should move to applicant name column. Using single sql query

Kalaiselvi Velusamy
  • 1
  • 2

3 Answers3

1

It may sounds funny, But you can easily alter the table and change the column name with correct labeling.

mkRabbani
  • 16,295
  • 2
  • 15
  • 24
  • That will "change" the values for **all** rows, not for just those that are wrong –  May 11 '19 at 08:29
  • Yes, Its applicable if the whole set of data is incorrect. – mkRabbani May 11 '19 at 08:34
  • @a_horse_with_no_name . . . As I read the question, it suggests that all the rows are affected. Swapping the values makes sense if only some values are swapped. However, if all are, this is a sensible solution. – Gordon Linoff May 11 '19 at 12:12
0
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);

Solution would be :

UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;

More info can be found here.

oreopot
  • 3,392
  • 2
  • 19
  • 28
  • This is a MySQL specific answer, the question however is tagged with `sql` which does not imply a specific DBMS but refers to standard SQL. (and e.g. the use of `@temp` is invalid standard SQL) –  May 11 '19 at 06:14
0

You can simply assign the names 

update the_table 
    set applicant_name = father_name, 
        father_name = applicant_name
where ...; -- make sure to only do that for the rows that need it

The SQL standard requires that the values used on the right side are evaluated before the assignment.

This works with every modern DBMS, but not with MySQL. See dexter's answer if you need a workaround for MySQL.

Online example: https://rextester.com/RIK34525