Swapping column values in MySQL table - Anil Labs – a Tech Blog: Exploring NodeJS, ReactJS, Databases, Oracle APEX, Ubuntu, PHP, and Email Technologies

Articles
- OS
  - Ubuntu
- Style & Scripts
  - JavaScript
  - Jquery
- Google App Engine
- Facebook
- General Category
- SEOSearch Engine Optimization
- Latest News
- Openinon about this blog
Database
- mysql
PHP
- Graphs or Charts
- MailSending mails from PHP and issues with the mails.
- PHP CodeSample code using PHP
- CMS
  - Drupal
  - JoomlaA CMS ( Content Management System )
  - WordPress
- Framework
  - CakePHPAbout CakePHP , it is a framework using MVC architecture.
  - CodeIgniter
  - Symfony
  - Smarty
- Shopping Cart
  - OsCommerce
  - X-CartX-Cart is shopping cart functionality using PHP.
Node.js
React.js
Mail
LowCode-NoCode
- Microsoft Power Platform
- Oracle APEX

In this post I would like to share my views on how swapping column values in MySQL table. Recently I have a requirement that one record should be active mode in the given point of time remaining records should be in inactive mode. I have got the solution for it. So I would like to share my views on it.

Swapping column values in MySQL table by Anil Kumar Panigrahi

Swapping column values in MySQL table by Anil Kumar Panigrahi

demo link for Swapping column values in MySQL table

MySQL table :

1
2
3
4
5

CREATE TABLE IF NOT EXISTS users (
id int(11) NOT NULL AUTO_INCREMENT,
status varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

Sample data to the table :

1
2
3
4
5
6

INSERT INTO users (id, status) VALUES
(1, 'inactive'),
(2, 'inactive'),
(3, 'inactive'),
(4, 'inactive'),
(5, 'active');

here we have one active user , remaining are in inactive status. Just I want to active the user id ‘2’ then run the below query:

MySQL Query :

1
2
3
4

update users a
inner join users b on a.id <> b.id
set a.status = b.status
where a.id in (2,5) and b.id in (2,5);

here swapped column values between 2, 5

Also see

Anil Labs -> MySQL

How to generate Data dictionary in MySql

How to import and export big database files

Categories: Databasemysql

1 Comment

How to import MySQL database with specific columns using phpMyAdmin - Anil Labs · August 19, 2023 at 2:16 pm

[…] phpMyAdmin, you can import a MySQL database and choose specific columns from a table using the following […]

Leave a Reply Cancel reply