To revert rows to default column value, let us first create a demo tablemysql> create table defaultDemo    -> (    -> Id int    -> ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into defaultDemo values(10); Query OK, 1 row affected (0.25 sec) mysql> insert into defaultDemo values(20); Query OK, 1 row affected (0.13 sec) mysql> insert into defaultDemo values(30); Query OK, 1 row affected (0.14 sec) mysql> insert into defaultDemo values(40); Query OK, 1 row affected (0.11 sec) mysql> insert into defaultDemo values(80); Query OK, ... Read More

Let us first create a tablemysql> create table recordsDemo    -> (    -> UserId int,    -> Value int    -> ); Query OK, 0 rows affected (0.52 sec)Now insert some records in the table using insert command.The query is as followsmysql> insert into recordsDemo values(1, 10); Query OK, 1 row affected (0.17 sec) mysql> insert into recordsDemo values(3, 598); Query OK, 1 row affected (0.18 sec) mysql> insert into recordsDemo values(5, 786); Query OK, 1 row affected (0.25 sec) mysql> insert into recordsDemo values(7, 189); Query OK, 1 row affected (0.16 sec) mysql> insert into recordsDemo values(9, 345); ... Read More

Let us see how to loop through a stored procedure in MySQLmysql> DELIMITER // mysql> CREATE PROCEDURE do_WhileDemo(LastValue INT)    -> BEGIN       -> SET @loop = 0;       -> REPEAT          -> SET @loop= @loop+ 1;          -> select @loop;          -> UNTIL @loop >LastValue       -> END REPEAT;    -> END // Query OK, 0 rows affected (0.17 sec) mysql> DELIMITER ;Now call the stored procedure with the help of CALL command.The query is as followsmysql> call do_WhileDemo(10);The following is the output+-------+ | ... Read More

You need to use rand() function to select random result from MySQL.The syntax is as followsselect *from yourTableName order by rand() limit 1;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table selectRandomRecord    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20)    -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into selectRandomRecord(StudentName) values('John'); Query OK, 1 row affected (0.17 sec) mysql> insert into selectRandomRecord(StudentName) values('Carol'); Query OK, ... Read More

Let us firs create a tablemysql> create table LimitWithStoredProcedure    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(10)    -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into LimitWithStoredProcedure(Name) values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert into LimitWithStoredProcedure(Name) values('Chris'); Query OK, 1 row affected (0.15 sec) mysql> insert into LimitWithStoredProcedure(Name) values('Maxwell'); Query OK, 1 row affected (0.28 sec) mysql> insert into LimitWithStoredProcedure(Name) values('Bob'); Query OK, 1 row affected (0.24 sec) mysql> insert into LimitWithStoredProcedure(Name) values('David'); Query ... Read More

The syntax is as followsupdate yourTableName set yourColumnName1=yourValue where yourColumnName2=yourValue order by yourIdColumnName DESC LIMIT 1;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table UpdateWithHighestDemo    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserStatus tinyint,    -> UserRank int    -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into UpdateWithHighestDemo(UserStatus, UserRank) values(1, 78); Query OK, 1 row affected (0.12 sec) mysql> insert into UpdateWithHighestDemo(UserStatus, UserRank) values(0, 118); Query ... Read More

You can use format() function for the separators. It will work in MySQL version greater than or equal to 5.5. We are using the version 8.0.12mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)The syntax is as followsSELECT FORMAT(yourColumnName, valueAfterDecimalPoint, 'de_DE') AS anyAliasNamefrom yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table formatNumberDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Number DECIMAL(19, 1) ... Read More

To convert number INT in minutes to TIME in MySQL, you can use SEC_TO_TIME() function.The syntax is as followsselect SEC_TO_TIME(yourIntColumnName*60) AS `anyAliasName` from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table convertNumberToMinute    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> NumberToMinute int    -> ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into convertNumberToMinute(NumberToMinute) values(60); Query OK, 1 row affected (0.12 sec) mysql> insert into convertNumberToMinute(NumberToMinute) values(70); Query ... Read More

You can use the COALESCE() function to convert MySQL null to 0SELECT COALESCE(yourColumnName, 0) AS anyAliasName FROM yourTableName;Let us first create a table. The query to create a table is as followsmysql> create table convertNullToZeroDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(20),    -> Salary int    -> ); Query OK, 0 rows affected (1.28 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into convertNullToZeroDemo(Name, Salary) values('John', NULL); Query OK, 1 row affected (0.20 sec) mysql> insert into convertNullToZeroDemo(Name, Salary) values('Carol', 5610); Query OK, 1 ... Read More

Update date of datetime field with the help of arithmetic operator minus(-).The syntax is as followsupdate yourTableName set yourDateTimeColumnName=yourDateTimeColumnName - interval yourValue day where date(yourDateTimeColumnName)=’yourDateValue’;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table updateDateDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ArrivalDate datetime    -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into updateDateDemo(ArrivalDate) values('2011-01-13'); Query OK, 1 row affected (0.19 sec) mysql> insert into updateDateDemo(ArrivalDate) values('2013-04-21'); ... Read More