Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQL Articles
Page 168 of 355
Update a column in MySQL and remove the trailing underscore values
To remove the trailing values, use TRIM() as in the below update syntax −update yourTableName set yourColumnName=trim(trailing '_' from yourColumnName);Let us first create a table −mysql> create table DemoTable1521 -> ( -> StudentCode varchar(20) -> ); Query OK, 0 rows affected (1.33 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1521 values('345_'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1521 values('12345'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable1521 values('9084_'); Query OK, 1 row affected (1.29 sec)Display all records from the table using select statement −mysql> select ...
Read MoreHow do I select data from one table only where column values from that table match the column values of another table in MySQL?
For this, you can use subquery along with EXISTS. Let us first create a table −mysql> create table DemoTable1 -> ( -> Id int, -> SubjectName varchar(20) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(111, 'MySQL'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1 values(112, 'MongoDB'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1 values(113, 'Java'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1 values(114, 'C'); Query OK, 1 row affected (0.27 sec) ...
Read MoreMySQL query to select rows one batch at a time
For this, you can use the concept of LIMIT and OFFSET. Let us first create a table −mysql> create table DemoTable1514 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> FirstName varchar(20) -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1514(FirstName) values('Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1514(FirstName) values('Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1514(FirstName) values('Sam'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable1514(FirstName) values('Mike'); Query OK, 1 row ...
Read MoreWhen inserting a new row, should I include the columns that are null in the MySQL query?
If you do not specify the column list in insert statement then you can use below syntax −insert into yourTableName values(NULL, yourValue, NULL, NULL, .....N);Let us first create a table −mysql> create table DemoTable1513 -> ( -> StudentId int, -> StudentName varchar(20) , -> StudentAge int, -> StudentCountryName varchar(20) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1513 values(NULL, 'Chris Brown', NULL, NULL); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1513 values(101, NULL, NULL, NULL); Query OK, 1 row ...
Read MoreHow do I detect if the ON UPDATE event fired with query in MySQL?
You can detect with the help of row_count(). If the row_count() returns 1 that means it is a new record. If it returns 2, that means the ON UPDATE event is fired with query. Following is the syntax −select row_count();Let us first create a table −mysql> create table DemoTable1512 -> ( -> Value int , -> UNIQUE(Value) -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1512 values(90) on duplicate key update Value=Value+10; Query OK, 1 row affected (0.09 sec)Now you can check the on ...
Read MoreHow to prevent MySQL GROUP BY from collapsing NULL values into a single row?
Fir this, you can use IFNULL() along with ORDER BY clause. Let us first create a table table −mysql> create table DemoTable1511 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> FirstName varchar(20) -> ); Query OK, 0 rows affected (1.97 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1511(FirstName) values('John'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1511(FirstName) values('Robert'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable1511(FirstName) values('Mike'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1511(FirstName) values('Robert'); Query OK, 1 ...
Read MoreMySQL update datetime column values and add 10 years 3 months 22 days and 10 hours, 30 minutes to existing data?
For this, you can use INTERVAL in MySQL. Let us first create a table −mysql> create table DemoTable1509 -> ( -> ArrivalTime datetime -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1509 values('2018-01-21 10:20:30'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1509 values('2019-04-01 11:00:00'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1509 values('2015-12-12 05:45:20'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select * from DemoTable1509;This will produce the following output ...
Read MoreShow a MySQL user-defined variables values in the result table?
Use @ for variable and concat_ws() to display concatenated result in the table. Let us first create a table −mysql> create table DemoTable1508 -> ( -> StudentFirstName varchar(20), -> StudentLastName varchar(20) -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1508 values('Chris', 'Brown'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1508 values('David', 'Miller'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1508 values('John', 'Doe'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> ...
Read MoreHow to make MySQL display results in a single line?
For this, you can use group_concat(). Let us first create a table −mysql> create table DemoTable1507 -> ( -> Name varchar(20), -> PaperSet int -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1507 values('Chris', 111); Query OK, 1 row affected (0.37 sec) mysql> insert into DemoTable1507 values('David', 112); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1507 values('Mike', 111); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1507 values('Bob', 113); Query OK, 1 row affected (0.14 sec)Display all records from ...
Read MoreRemove space between two words in MySQL?
For this, you can use REPLACE(). Let us first create a table −mysql> create table DemoTable1506 -> ( -> Title text -> ); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1506 values('This is MySQL'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1506 values('This is Java language'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1506 values('This is MongoDB NoSQL database'); Query OK, 1 row affected (0.60 sec)Display all records from the table using select statement −mysql> select * from DemoTable1506;This will ...
Read More