MySQL - updating and deleting data
Updating Database Data syntax
with an example.
Ignoring Update Errors syntax
with an example.
Deleting Database Data syntax
with an example.
List out guidelines for
updating and deleting data in MySQL.
MySQL updating and deleting data - Dec 13, 2008 at 22:00 PM by
Rajmeet Ghai
Updating Database Data syntax with an example.
Update: Update statement is used to update existing data (row)
in a table. It is used to update some column value by specifying a condition.
Syntax:
Update table_name
Set column_name1= ‘value’, column_name2=’value2’
Where column_name3=’value3’
Example:
Update customer
Set first_name=’john’
Where last_name=’james’;
Ignoring Update Errors syntax with an example.
Using the IGNORE keyword, update statement is executed and does not abort even
if it encounters any errors. If any rows for which duplicate key issues occur
they are ignored and rows which may cause any data conversion errors are
updated with the closest values.
Syntax:
Update IGNORE table_name
Set column_name1= ‘value’, column_name2=’value2’
Where column_name3=’value3’
Deleting Database Data syntax with an example.
The database can be deleted using the DROP DATABASE syntax. It drops all tables.
The user needs the DROP privilege to drop the database.
Example:
DROP DATABASE IF EXISTS sample;
Here, the IF EXISTS is used to prevent errors in case the database does not
exist.
List out guidelines for updating and deleting data in MySQL.
-
Updating and deleting data must be avoided without a where clause
-
It should be ensured that every table has a primary key
-
Before updating or delete, use the select clause to ensure the right data is
returned
-
The database enforced referential integrity must be obeyed to avoid the
deletion of rows that have data in other tables related to them.
|
