Update 'a' record with 'b' and 'b' with 'a' in a MySQL column (swap) with only 'a' and 'b' values?

For this, use a CASE statement. Let us first create a table −

mysql> create table DemoTable
(
   Value char(1)
);
Query OK, 0 rows affected (1.21 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('a');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('b');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('a');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values('a');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('b');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values('a');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('b');
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement

mysql> select *from DemoTable;

This will produce the following output −

+-------+
| Value |
+-------+
| a     |
| b     |
| a     |
| a     |
| b     |
| a     |
| b     |
+-------+
7 rows in set (0.00 sec)

Following is the query to update 'a' record with 'b' and 'b' with 'a' −

mysql> update DemoTable set Value=case when Value='a' then 'b' else 'a' end;
Query OK, 7 rows affected (0.17 sec)
Rows matched: 7 Changed: 7 Warnings: 0

Let us check the table records once again −

mysql> select *from DemoTable;

This will produce the following output −

+-------+
| Value |
+-------+
| b     |
| a     |
| b     |
| b     |
| a     |
| b     |
| a     |
+-------+
7 rows in set (0.00 sec)

AmitDiwan

Updated on 25-Sep-2019 11:11:22

• Related Questions & Answers
• Python a += b is not always a = a + b
• Larger of a^b or b^a in C++
• Find FIRST & FOLLOW for the following Grammar.\nS → A a A | B b B\nA → b B\nB → ε
• Update a column A if null, else update column B, else if both columns are not null do nothing with MySQL
• Prefixes with more a than b in C++
• Print values of ‘a’ in equation (a+b) <= n and a+b is divisible by x
• Construct a Finite Automata for the regular expression ((a+b)(a+b))*.
• Annotate Subplots in a Figure with A, B, C using Matplotlib
• Show that the following grammar is LR (1)\nS → A a |b A c |B c | b B a\nA → d\nB → d
• MySQL UPDATE column names and set None values with N/A?
• Count number of pairs (A <= N, B <= N) such that gcd (A , B) is B in C++
• a.sort, sorted(a), np_argsort(a) and np.lexsort(b, a) in Python
• Construct Quadruples, Triples, and Indirect Triples for the expression\n-(a + b) * (c + d) - (a + b + c)
• Check if a + b = c is valid after removing all zeroes from a, b and c in C++
• Count number of triplets (a, b, c) such that a^2 + b^2 = c^2 and 1<=a<=b<=c<= n in C++