MySql Delete Rows

MySql Delete Rows

MySql delete command is used to delete the one or more rows in mysql table.

DELETE is a data manipulation language (DML) command.

where clause supported with DELETE command.

We have to issue COMMIT and ROLLBACK commands with DELETE command to confirm our changes.

MySql delete command syntax,

DELETE FROM <table-name> WHERE &t;column-condition>;

Deletes all the rows which are matching the conditions specified in table name used in sql delete query.

Let us consider sample table 'tblemployee' with below records.

mysql> select * from tblemployee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

Delete the particular row in mysql table using WHERE clause condition.

mysql> delete from tblemployee where emp_id=4;

Output:

Query OK, 1 row affected (0.38 sec)

Lets check the mysql table 'tblemployee' after running the delete query.

here above delete query deletes the row which is having emp_id 4.

mysql> select * from tblemployee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
+--------+----------+---------+--------+
3 rows in set (0.00 sec)

copy deleted rows from duplicate table 'tblemployee1' and insert into 'tblemployee' table.

mysql> insert into tblemployee (emp_id, emp_name, dept_id, salary) select * from tblemployee1 where emp_id=4
    -> ;

Output:

Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Now we have 4 records in mysql table 'tblemployee'.

mysql> select * from tblemployee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

mysql>

Delete multiple rows using where clause

Deletes the multiple rows which are matching the condition speciifed in where clause.

mysql> delete from tblemployee where dept_id=10;
Query OK, 2 rows affected (0.02 sec)

mysql> select * from tblemployee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      2 | emp2     |      11 |  20000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
2 rows in set (0.00 sec)

mysql>

Delete command without where clause

Deletes all the rows if where clause is not used in mysql delete query.

mysql> select * from tblemployee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      2 | emp2     |      11 |  20000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
2 rows in set (0.00 sec)

mysql> delete from tblemployee;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from tblemployee;
Empty set (0.00 sec)

mysql>

« Previous Next »