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
Database Articles
Page 391 of 547
Update only a single column in a MySQL table and increment on the basis of a condition
Let us first create a table −mysql> create table DemoTable ( Name varchar(50), Score int ); Query OK, 0 rows affected (1.02 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Sam', 45); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('Mike', 28); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable values('Carol', 27); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('David', 67); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the ...
Read MoreMySQL time period query to fetch date records from interval of 14 weeks from current date?
For this, you can use the BETWEEN keyword. Let us first create a table −mysql> create table DemoTable ( ArrivalDate date ); Query OK, 0 rows affected (0.93 sec)Let’s say the current date is 2019-08-31.Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-04-21'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2019-03-01'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('2019-09-01'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('2019-08-31'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2019-06-30'); Query OK, 1 ...
Read MoreHow to insert NULL into char(1) in MySQL?
For this, you need to set sql_mode to 'STRICT_TRANS_TABLES’. This mode issues a warning when an invalid value is inserted but inserts the same value. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(50), Gender char(1) NULL ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using insert command −mysql> set sql_mode = 'STRICT_TRANS_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into DemoTable(Name, Gender) select 'Chris', NULL ; Query OK, 1 row affected (0.21 sec) Records: 1 Duplicates: ...
Read MoreORDER BY records in MySQL based on a condition
For this, you can use ORDER BY IF(). Let us first create a table −mysql> create table DemoTable ( Name varchar(50), Score int ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 98); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('David', 45); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Bob', 56); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Sam', 89); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Carol', 78); Query ...
Read MoreFetch similar ID records from two tables in MySQL
Let us first create a table −mysql> create table DemoTable1 ( Id int ); Query OK, 0 rows affected (1.26 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(100); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1 values(110); Query OK, 1 row affected (0.49 sec) mysql> insert into DemoTable1 values(4); Query OK, 1 row affected (0.44 sec) mysql> insert into DemoTable1 values(3); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+------+ | Id | ...
Read MoreMySQL query to merge rows if Id is the same and display the highest corresponding value from other columns
For this, use aggregate function MAX() along with the GROUP BY clause. Let us first create a table −mysql> create table DemoTable ( Id int, Value1 int, Value2 int, Value3 int, Value4 int ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Id, Value4) values(100, 30); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Id, Value1, Value2, Value3) values(100, 20, 60, 40); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Id, Value2, Value3, Value4) values(100, 90, 100, 110); Query OK, 1 ...
Read MoreMySQL query to fetch records with arrangement in the form of numbers and letter like 99S, 50K, etc.?
The easiest way to achieve this is by using REGEXP. Let us first create a table −mysql> create table DemoTable ( Id varchar(50) ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John123'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values('123Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('99Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('19David'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('99S'); Query OK, 1 row affected (0.18 sec) ...
Read MoreReplace a specific duplicate record with a new value in MySQL
Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(50) ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name) values('Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name) values('Robert'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name) values('Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(Name) values('David'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(Name) values('Mike'); Query OK, 1 row affected (0.13 sec)Display all records ...
Read MoreCan we use the result of a SUM() function in MySQL WHERE clause
We can use the HAVING clause rather than the WHERE in MySQL. Let us first create a table −mysql> create table DemoTable ( Name varchar(50), Price int ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 30); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('David', 40); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Chris', 10); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Mike', 44); Query OK, 1 row affected (0.14 sec) mysql> insert into ...
Read MoreMySQL date comparison to fetch dates between a given range?
Let us first create a table −mysql> create table DemoTable ( AdmissionDate date ); Query OK, 0 rows affected (0.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-08-31'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('2019-09-01'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2019-05-10'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('2019-06-12'); Query OK, 1 row affected (0.25 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+---------------+ | AdmissionDate | +---------------+ ...
Read More