REPLACE and DELETE statements in MySQL | Replacing and Deleting Data in MySQL

REPLACE statement in MySQL

The REPLACE statement is used to replace any row in a table. The REPLACE statement works as follows:

• If the duplicate key error doesn't occur, it simply works like an INSERT statement and inserts the row into the table.
• If the insertion fails due to the duplicate key error, it deletes the error-causing row from the table and inserts the new row with the given data into the table.

To determine the duplicate key error while replacing a row, MySQL uses Primary Key or Unique Key index.

Syntax:

REPLACE INTO table_name(column1, column2,...)
VALUES(value1, value2,....)

For example:

The above table contains the details of the students.

REPLACE INTO students (roll_no, marks)
VALUES(2,98);

The modified table after the execution of the above query will be:

It can be seen in the table that, in the 2nd-row 2nd-column 'Ankit' is replaced by ‘NULL’. This is because while executing the above query duplicate key error occurs as roll_no 2 is already present in the table. So, the 2nd row is deleted and a new row with new data is inserted and there is no value assigned to the name column in the above query, so it takes the default value ‘NULL’.

Using REPLACE Statement to Update a row

The REPLACE statement can be used to update a row in a table. It can be done as follows:

REPLACE INTO students
SET roll_no=4,name='Vivek',marks=95;

The above query when executed on the original given students table. The modified table will be:

DELETE Statement in MySQL

The DELETE statement is used in MySQL to delete data from a table.

Syntax:

 DELETE FROM table_name
 WHERE condition;

In the above-written syntax:

• table_name specifies the table from which we want to delete the data.
• WHERE clause is used after that to select the rows which we want to delete from the table. The rows which fulfill the condition are deleted from the table.
• If the WHERE clause is not used, all the rows will be deleted from the table.

For example:

The above table contains the details of the cricketers.

DELETE FROM cricketers
WHERE country='Australia';

The above query will delete all the rows having ‘Australia’ in the country column. The table after the execution of the above query will look like below:

DELETE and LIMIT clause in MySQL

LIMIT clause is used with the DELETE  in MySQL to limit the number of rows we want to delete from the table.

Syntax:

DELETE FROM table_name
WHERE condition
ORDER BY column1,column2,column3….
LIMIT row_count;

In the above syntax:

• table_name is the name of the table from which we want to delete the data.
• Condition is applied with the WHERE the clause to choose specific rows we want to delete.
• ORDER BY is used to sort the rows in any specified order because the order of rows in a table is unspecified.
• LIMIT is used to limit the number of rows that we want to delete which fulfill the given condition, row_count is any number.

For example:

DELETE FROM cricketers
WHERE country='Australia'
ORDER BY Name
LIMIT 2;

The above query will delete ‘2’ rows from the ‘cricketers’ table having country = ‘Australia’, arranged in ascending order according to name. Now, the table will look like shown below:

It can be seen from the above table, two rows having country ‘Australia’ are deleted which appeared alphabetically before the third one.