DELETE WITH LIMIT helps when there are duplicate records in the DB and you want to keep only few records from them and wanted to delete the rest of all.
LIMIT with DELETE Query :-
mysql> select * FROM MAIL WHERE USER='srinivas';
+----+----------+-------+----------+----------+
| ID | USER | SIZE | srcuser | EMPID |
+----+----------+-------+----------+----------+
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
+----+----------+-------+----------+----------+
8 rows in set (0.00 sec)
mysql> select * FROM MAIL WHERE USER='srinivas' ORDER BY 1 LIMIT 3;
+----+----------+-------+----------+----------+
| ID | USER | SIZE | srcuser | EMPID |
+----+----------+-------+----------+----------+
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
+----+----------+-------+----------+----------+
3 rows in set (0.00 sec)
mysql> DELETE FROM MAIL WHERE USER='srinivas' ORDER BY ID LIMIT 3;
Query OK, 3 rows affected (0.00 sec)
mysql> select * FROM MAIL WHERE USER='srinivas';
+----+----------+-------+----------+----------+
| ID | USER | SIZE | srcuser | EMPID |
+----+----------+-------+----------+----------+
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
+----+----------+-------+----------+----------+
5 rows in set (0.00 sec)
mysql> DELETE FROM MAIL WHERE USER='srinivas' LIMIT 3;
Query OK, 3 rows affected (0.00 sec)
mysql> select * FROM MAIL WHERE USER='srinivas';
+----+----------+-------+----------+----------+
| ID | USER | SIZE | srcuser | EMPID |
+----+----------+-------+----------+----------+
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
+----+----------+-------+----------+----------+
2 rows in set (0.00 sec)
SQL_CALC_FOUND_ROWS & FOUND_ROWS() to know the number of rows present in the table with the combination of LIMIT Option ( Substitute of COUNT(*))
mysql> select SQL_CALC_FOUND_ROWS * from MAIL_bkp LIMIT 1;
+----+----------+-------+----------+----------+
| ID | USER | SIZE | srcuser | EMPID |
+----+----------+-------+----------+----------+
| 1 | srinivas | 23.23 | srinivas | 12121212 |
+----+----------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 15 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM MAIL_bkp;
+----------+
| COUNT(*) |
+----------+
| 15 |
+----------+
1 row in set (0.00 sec)
VIEW Syntax :-
mysql> select * from MAIL_bkp ;
+----+----------+-------+----------+----------+
| ID | USER | SIZE | srcuser | EMPID |
+----+----------+-------+----------+----------+
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 4 | mahi | 53.23 | mahi | 12121212 |
| 3 | suma | 3.23 | suma | 12121212 |
| 2 | neethu | 13.23 | neethu | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 2 | neethu | 13.23 | neethu | 12121212 |
| 3 | suma | 3.23 | suma | 12121212 |
| 4 | mahi | 53.23 | mahi | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 1 | srinivas | 23.23 | srinivas | 12121212 |
| 0 | PARTHA | 45 | MAITY | 12121212 |
+----+----------+-------+----------+----------+
15 rows in set (0.00 sec)
mysql> desc MAIL_bkp;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| ID | int(10) unsigned | NO | | NULL | |
| USER | varchar(255) | NO | | | |
| SIZE | float unsigned | YES | | 0 | |
| srcuser | varchar(8) | YES | | NULL | |
| EMPID | int(20) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
5 rows in set (0.00 se
mysql> CREATE VIEW MAIL_VIEW AS SELECT ID, SIZE*1024*1024 'Size in Gigs' FROM MAIL_bkp;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from MAIL_VIEW;
+----+--------------+
| ID | Size in Gigs |
+----+--------------+
| 1 | 24358420 |
| 4 | 55815700 |
| 3 | 3386900.5 |
| 2 | 13872660 |
| 1 | 24358420 |
| 2 | 13872660 |
| 3 | 3386900.5 |
| 4 | 55815700 |
| 1 | 24358420 |
| 1 | 24358420 |
| 1 | 24358420 |
| 1 | 24358420 |
| 1 | 24358420 |
| 1 | 24358420 |
| 0 | 47185920 |
+----+--------------+
15 rows in set (0.00 sec)
mysql> desc MAIL_VIEW;
+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| ID | int(10) unsigned | NO | | NULL | |
| Size in Gigs | double | YES | | NULL | |
+--------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
c)
CONCAT
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| ID | int(10) unsigned | NO | | NULL | |
| USER | varchar(255) | NO | | | |
| SIZE | float unsigned | YES | | 0 | |
| srcuser | varchar(8) | YES | | NULL | |
| EMPID | int(20) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select CONCAT( "Hello ",USER, " your Salary is: ", size, "USD") MESSAGE from MAIL_bkp;
+------------------------------------------+
| MESSAGE |
+------------------------------------------+
| Hello srinivas your Salary is: 23.23USD |
| Hello mahi your Salary is: 53.23USD |
| Hello suma your Salary is: 3.23USD |
| Hello neethu your Salary is: 13.23USD |
| Hello srinivas your Salary is: 23.23USD |
| Hello neethu your Salary is: 13.23USD |
| Hello suma your Salary is: 3.23USD |
| Hello mahi your Salary is: 53.23USD |
| Hello srinivas your Salary is: 23.23USD |
| Hello srinivas your Salary is: 23.23USD |
| Hello srinivas your Salary is: 23.23USD |
| Hello srinivas your Salary is: 23.23USD |
| Hello srinivas your Salary is: 23.23USD |
| Hello srinivas your Salary is: 23.23USD |
| Hello PARTHA your Salary is: 45USD |
+------------------------------------------+
15 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> select CONCAT( "Hello ",USER, " your Salary is: ", size, " USD") MESSAGE from MAIL_bkp;
+-------------------------------------------+
| MESSAGE |
+-------------------------------------------+
| Hello srinivas your Salary is: 23.23 USD |
| Hello mahi your Salary is: 53.23 USD |
| Hello suma your Salary is: 3.23 USD |
| Hello neethu your Salary is: 13.23 USD |
| Hello srinivas your Salary is: 23.23 USD |
| Hello neethu your Salary is: 13.23 USD |
| Hello suma your Salary is: 3.23 USD |
| Hello mahi your Salary is: 53.23 USD |
| Hello srinivas your Salary is: 23.23 USD |
| Hello srinivas your Salary is: 23.23 USD |
| Hello srinivas your Salary is: 23.23 USD |
| Hello srinivas your Salary is: 23.23 USD |
| Hello srinivas your Salary is: 23.23 USD |
| Hello srinivas your Salary is: 23.23 USD |
| Hello PARTHA your Salary is: 45 USD |
+-------------------------------------------+
15 rows in set (0.00 sec)
mysql> select 34<>23;
+--------+
| 34<>23 |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
mysql> select 34!=23;
+--------+
| 34!=23 |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
mysql> select 34=23;
+-------+
| 34=23 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)
mysql>
Ask Question
