Difference between revisions of "Oracle:DELETE"
From mi-linux
Jump to navigationJump to search (Created page with "Main Page >> Oracle and SQL >> Workbook >> Deleting records == DELETE Command == The format of the SQL - DELETE statement is: <nowiki> DE...") |
|||
| (8 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | [[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|Workbook]] >> Deleting records | + | [[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|Workbook]] >> [[Oracle:Data_Manipulation_Language|DML]] >> Deleting records |
== DELETE Command == | == DELETE Command == | ||
| Line 5: | Line 5: | ||
The format of the SQL - DELETE statement is: | The format of the SQL - DELETE statement is: | ||
| − | < | + | <code> |
| − | DELETE FROM TableName | + | :DELETE FROM TableName |
| − | + | ::[WHERE FilterCondition1 | |
| − | + | ::[AND | OR FilterCondition2 ...]] | |
| − | </ | + | </code> |
Where: | Where: | ||
| − | FROM TableName Specifies the table in which records are deleted from. | + | *''FROM TableName'' Specifies the table in which records are deleted from. |
| − | WHERE FilterCondition1 [AND | OR FilterCondition2 ...] FilterCondition specifies the criteria that records must meet to be deleted. You can include as many filter conditions as you like, connecting them with the AND or OR operator. | + | *''WHERE FilterCondition1 [AND | OR FilterCondition2 ...]'' The FilterCondition specifies the criteria that records must meet to be deleted. |
| + | |||
| + | You can include as many filter conditions as you like, connecting them with the AND or OR operator. | ||
| + | |||
You can also use the NOT operator to reverse the value of a logical expression, or use IS NULL to check for an empty field. | You can also use the NOT operator to reverse the value of a logical expression, or use IS NULL to check for an empty field. | ||
| + | |||
Our new employee KING has decided to leave, to remove the record from the EMP table type: | Our new employee KING has decided to leave, to remove the record from the EMP table type: | ||
| − | DELETE FROM EMP | + | DELETE FROM EMP |
| − | WHERE ENAME = 'KING' | + | WHERE ENAME = 'KING' |
| − | AND EMPNO = 7945; | + | AND EMPNO = 7945; |
| − | COMMIT; | + | COMMIT; |
| − | + | ||
| + | == Exercise 3.4 == | ||
| + | |||
| + | 3.4.1 Why do we need to specify a number as well as the name when deleting the record? | ||
| + | 3.4.2 Could the WHERE statement be shortened? | ||
| − | + | 3.4.3 What command verifies the record has been deleted? | |
| + | The system should respond with the message: | ||
| − | + | <pre style="color: blue"> | |
| + | - no rows selected | ||
| + | </pre> | ||
| + | |||
| + | |||
| + | == Next Step == | ||
| − | + | [[Oracle:UPDATE|Updating the database]]. | |
| − | |||
Latest revision as of 15:51, 2 March 2016
Main Page >> Oracle and SQL >> Workbook >> DML >> Deleting records
DELETE Command
The format of the SQL - DELETE statement is:
- DELETE FROM TableName
- [WHERE FilterCondition1
- [AND | OR FilterCondition2 ...]]
Where:
- FROM TableName Specifies the table in which records are deleted from.
- WHERE FilterCondition1 [AND | OR FilterCondition2 ...] The FilterCondition specifies the criteria that records must meet to be deleted.
You can include as many filter conditions as you like, connecting them with the AND or OR operator.
You can also use the NOT operator to reverse the value of a logical expression, or use IS NULL to check for an empty field.
Our new employee KING has decided to leave, to remove the record from the EMP table type:
DELETE FROM EMP WHERE ENAME = 'KING' AND EMPNO = 7945; COMMIT;
Exercise 3.4
3.4.1 Why do we need to specify a number as well as the name when deleting the record?
3.4.2 Could the WHERE statement be shortened?
3.4.3 What command verifies the record has been deleted?
The system should respond with the message:
- no rows selected
