MySQL Server : Open Source Database : Insert/Update/Delete Record
| MySQL Insert Record |
The INSERT Command
Inserting Data in a MySQL Database Once tables have been created, the database sits like an empty container.
To insert a record into a table, you must specify values for all fields that do not have default values and cannot be NULL.
Syntax :
INSERT INTO table (columns) VALUES (values);
The above statement can be excluded if all required columns are inserted and the values are listed in the same order as the columns in the table as.
Example :
INSERT INTO studio VALUES (1101,'Rumuji Studios');
We recommend you include the second line all the time though as the code will be easier to read and update and less likely to break as the database is modified.
Using an INSERT statement
Individual or multiple rows are added to a single table on a row-by-row basis, specifying one value per column.
If there are fewer values than there are columns, NULL or DEFAULT values are substituted for missing values.
Insert Statement Syntax:
<insert statement>::=
INSERT [IGNORE] [INTO]
{<values option> | <set option> | <select option>}
<values option>::=
<table name> [(<column name> [{, <column name>}...])]
VALUES ({<expression> | DEFAULT} [{, {<expression> | DEFAULT}}...])
[{, ({<expression> | DEFAULT} [{, {<expression> | DEFAULT}}...])}...]
<set option>::=
<table name>
SET <column name>={<expression> | DEFAULT}
[{, <column name>={<expression> | DEFAULT}}...]
<select option>::=
<table name> [(<column name> [{, <column name>}...])]
<select statement>
Add Multiple Rows
The next example adds multiple rows to studio at the same time.
INSERT INTO studio (studio_id,studio_name)
VALUES (1,'Paramount Pictures'),(2,'MGM Pictures'),(3,'Rumuji Studios');
Tip:The values for studio_id are explicitly specified here, so no AUTO_INCREMENT values are used. This technique is usually used to preload a table with pre-determined ID values where pre-determined ID values are actively used for searching. It may be best not to use AUTO_INCREMENT and set known values into the ID field oneself. One can set the AUTO_INCREMENT to a high value for any future dynamic values.
- The <set option> in an INSERT statement allows for direct pairing of columns and expressions being set into the columns.
- This prevents any column ordering issues and provides an explicit assignment, hence correctness without having to refer back and forth between column list and VALUES clause.
- The <set option> alternative is very helpful when only selected columns are receiving values and defaults are used for the rest.
- The INSERT statement below uses a SET clause where for each value, we give the column name, an equal (=) sign, and an expression or the DEFAULT keyword. The <expression> option and the DEFAULT keyword work same as previous INSERT statement.
Example :
INSERT INTO film_review
SET review_id=NULL, film_id=999, review_by='Film Press',
review_text='Movie of the year',
review_date='2007-07-02',
review_category=DEFAULT;
Limitation: The <SET option> alternative does not allow us to insert multiple rows with one statement as in
INSERT with ON DUPLICATE KEY UPDATE Syntax
The ON DUPLICATE KEY UPDATE clause of INSERT has a REPLACE like effect where if a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed instead.
Similar to a REPLACE statement, using ON DUPLICATE KEY UPDATE causes the affected-rows value per row to be 1 if the row is inserted as a new row or 2 if an existing row is updated.
Example :
INSERT INTO availability (film_id, in_stock)
SELECT film_id, 1 FROM inventory ON DUPLICATE KEY UPDATE in_stock=in_stock+1;
The film_id column is not unique in inventory, but is unique (PK) in availability table. Hence as rows are selected from inventory to be inserted into availability, we will start hitting duplicates at some point. Using ON DUPLICATE KEY UPDATE, the in_stock count will be simply incremented by 1 on duplicates, as intended.
| MySQL Replace Record |
The REPLACE Command
We can use SET clause in REPLACE statement as well.
Example:
REPLACE INTO film_review
SET review_id=12, film_id=1997, review_by='Film Press',
review_text='Movie of the year (to avoid)',
review_date='2007-06-22',
review_category='PROF';
The REPLACE using SET clause will insert or overwrite a review for given review_id, showing 1 or 2 rows affected as is the case.
| MySQL Update Record |
The UPDATE Statement
The primary statement used to modify existing data in a SQL database is an UPDATE statement, though REPLACE above is a viable option. The UPDATE statement lets us modify one or more fields for one or more records in a table.
Syntax
UPDATE table SET field = value [,field = value] [WHERE conditions];
Example:
UPDATE film_review SET film_id=1997,review_by='Film Press',
review_text='Movie of the year (to avoid)',review_date='2007-06-22',
review_category='PROF‘ WHERE review_id=212;
ORDER BY and LIMIT Clauses in UPDATE
MySQL allows for for a slightly advanced version of UPDATE as shown:
Syntax
UPDATE <table name> SET ... [WHERE ...]
[ORDER BY <column name> [ASC | DESC]
[{, <column name > [ASC | DESC]}...]]
[LIMIT <row count>]
Example:
UPDATE film_reserve
SET customer_id = 1
WHERE customer_id IS NULL LIMIT 5;
A customer with ID=1 wants to reserve any 5 movies that are not already reserved:
| MySQL Delete Record |
The DELETE Command
There are times when some data is not needed any longer and may even pose problems by remaining in the database. To discard unwanted data from a database, we will use DELETE statement, that allows you to delete one or more records in a table:
Syntax
DELETE FROM <table-name> WHERE <conditions>;
Warning: As with UPDATE, we must be very careful to limit deletes to intended records only.
Example:
DELETE FROM rental WHERE customer_id = 2190;
--Here is a more detailed syntax for DELETE with some MySQL extensions:
Syntax
DELETE [IGNORE] [LOW_PRIORITY] [QUICK]
FROM <table name>
[WHERE <where definition>]
[ORDER BY <column name> [ASC | DESC] [{, <column name>
[ASC | DESC]}...]][LIMIT <row count>]
Some Advanced MySQL modifiers to DELETE statement
- Using the LOW_PRIORITY option, the DELETE statement does not execute until various client connections are done accessing the target table. This affects only storage engines that use only table-level locking such as MyISAM.
- Using IGNORE option, any delete errors are replaced with warnings when deleting rows, allowing the rest of the statements in the script to execute.
- For MyISAM tables, the use of the QUICK keyword may speed up some delete operations, as the storage engine does not merge index leaves during delete.
- One can also use an ORDER BY to sort which rows get deleted first, and a LIMIT clause to limit deletes to number of rows specified.
ORDER BY and LIMIT in DELETE Statement
As with updates before, DELETE statement can be further qualified by using an ORDER BY and a LIMIT clause, as shown with the reservations example below:
Example:
DELETE FROM film_reserve WHERE customer_id = 1
ORDER BY reserve_date ASC LIMIT 5;
A customer has made too many reservations, and has asked to reduce their reservations by 5. The reservations for given customer are sorted in ascending order of Reservation Date, so 5 oldest reservations are deleted first. The LIMIT clause restricts the deletion to only five or less rows.
Using TRUNCATE Statement to Delete Data
The TRUNCATE statement cleans up a table by removing all rows, and no qualification or filteration can be specified.
Example:
TRUNCATE TABLE film_review;
-- same as -- DELETE FROM film_review;
In TRUNCATE statement auto_increment will start from one while in DELETE statement your last increment id will retain
Joining Tables in an UPDATE Statement
- MySQL also offers the
alternative as shown. The join is based on the foreign key relationship established between joined tables. - MySQL allows you to use any join definition in your UPDATE statement. JOINs are discussed further in another lesson. To reflect the ability to use different types of join definitions, the syntax for the UPDATE statement is shown to use join definitions:
- The syntax here shows only those components relevant to a multiple-table update. The original syntax uses a basic join for updating data from one table into another.
Syntax
UPDATE [LOW_PRIORITY] [IGNORE]<join definition>
SET <column name>=<expression> [{, <column name>=<expression>}...]
[WHERE <where definition>]
Warning:Joined Update alternative does not allow you an ORDER BY or a LIMIT clause.
Updating Multiple Tables using Joins
We can update more than one value in joined tables. Here is an example:
Example
UPDATE rental_detail, customer_detail
SET rental_detail.return_date = NOW(),
customer_detail.payment = customer_detail.payment + @rental_rate
WHERE rental_detail.customer_id = customer_detail.customer_id
AND rental_detail.rental_id = @rental_id
AND rental_detail.return_date IS NULL;
Update both rental_detail and customer_detail tables.
For the given rental, payment in customer_detail is increased and return_date in rental_detail is updated with current date.
In other words, we are able to update both tables participating in the join condition.
Maintaining Relationships
- Table relationships defined via foreign keys affect addition of data via inserts.
- For example, in a default configuration of a foreign key, we will receive an error while adding a row to a child table if the referencing column of the inserted row contains a value that does not exist in the referenced column of the parent table.
| Download Examples |
Recent Comments