DML Statements - Updating Data || DBMS Tutorial 8

DML Statements - Updating Data

DBMS Tutorial 8

Data Manipulation Language (DML):

Data Manipulation Language (DML) enables users to access or manipulate data in a relational database. DML provides commands for retrieval, creation, deletion, and modification of information in a database. DML requires a user to specify what data are needed without specifying how to get it. The database engine is left to figure out effective means of retrieving data.

Update Statement Syntax:

The Update Statement is used to modify existing records in a single table in a relational database.

Update statement can be represented as:

The database system ensures that no constraints are violated during the execution of an update statement. Any violation of constraints results in failure of the statement.

Employee Table:

Without WHERE Clause:

The Update statement without the WHERE clause is used to update all rows in a table. This is rarely used in real-life scenarios.

Query:

UPDATE Employee SET SALARY = SALARY * 1.10

Output:
5 rows updated.

Single Column Update:

The Update statement with the WHERE clause is used to update only those rows that satisfy the filter criteria.

Query:

UPDATE Employee SET SALARY = SALARY * 1.20 WHERE ID = 2

Output:
1 row updated.

Multiple Column Update:

Multiple columns can be updated in a single update statement.
Query:

UPDATE Employee SET SALARY = SALARY * 1.3, BONUS = SALARY * 0.30 WHERE ID = 1

Output:
1 row updated.

-
-

Duplicate Column Update:

The Update statement fails if the same column is updated multiple times in the same statement.
Query:

UPDATE Employee SET SALARY = 100, SALARY = 200 WHERE ID = 1

Output:
ORA-00957: duplicate column name

Multiple conditions in WHERE:

Just like a SELECT query, there is no limit to filter conditions that can be provided in the WHERE clause.

Query:

UPDATE Employee SET SALARY = SALARY * 1.40 WHERE DESIGNATION = 'SE' OR DEPT = 'ETA'

Output:
3 rows updated.

Incorrect Syntax:

A very common mistake is to add the TABLE keyword while writing the update statement.
Query:

UPDATE TABLE Employee SET Salary = Salary * 2

Output:
ORA-00903: invalid table name

Update Statement:

The metadata for the Employee table is as shown below:

Employee Table:

NOT NULL Violation:

Value of a column with a NOT NULL constraint cannot be updated to NULL.
Query:

UPDATE Employee SET ENAME = NULL WHERE ID = 1

Output:
ORA-01407: cannot update ("P65593220"."EMPLOYEE"."ENAME") to NULL

Primary Key Update:

All columns in a table including the PRIMARY KEY can be updated using the UPDATE statement.
Query:

UPDATE Employee SET ID = 6 WHERE ID = 5

Output:
1 row updated.

-
-

Primary Key Violation:

The Update statement fails when the primary key constraint is violated.
Query:

UPDATE Employee SET ID = 1 WHERE ID = 2

Output:
ORA-00001: unique constraint (P65593220.SYS_C008934130) violated

CHECK Constraint Violation:

The Update statement fails when the check constraint is violated.
Query:

UPDATE Employee SET DEPT = 'ILI' WHERE ID = 1

Output:
ORA-02290: check constraint (P65593220.SYS_C008934129) violated

Unique Key Violation:

The Update statement fails when the unique constraint is violated.
Query:

UPDATE Employee SET COMPID = 1001 WHERE ID = 2

Output:
ORA-00001: unique constraint (P65593220.SYS_C008934131) violated

Quiz 68: Consider the following table structure and content for 'Student' table:

Which of the following UPDATE statements will execute successfully for the Student table?

a) UPDATE Student SET Gender = 'Male' WHERE Id = 1
b) UPDATE Student SET Gender = 'X' WHERE Id = 1
c) UPDATE Student SET Id = NULL WHERE Name = 'Alice'
d) UPDATE Student SET Gender = 'F', DOJ = NULL WHERE Name = 'Alice' ---

Quiz 69: Consider the 'Student' table as :
Which of the following UPDATE statements will Update 2 rows for the Student table?

a) UPDATE Student SET DOJ = SYSDATE WHERE Gender = 'M' ---
b) UPDATE Student SET DOJ = SYSDATE WHERE Id = 1
c) UPDATE Student SET Id = NULL WHERE Gender = 'M'
d) UPDATE Student SET Name = 'Heena' WHERE Gender = 'F' AND Id = 4

-
-

Foreign Key Violation:

Employee Table

Computer Table

Foreign Key Violation - Child table:

Any attempt to update a record with values that do not exist in the referenced table will result in a failure

Query:

UPDATE Employee SET COMPID = 1005 WHERE ID = 1

Output:
ORA-02291: integrity constraint (P65593220.SYS_C008934133) violated - parent key not found

Foreign Key Violation - Master table:

Update statement fails if any attempt is made to update a value in the master table that is referenced in child tables.

Query:

UPDATE Computer SET COMPID = 1005 WHERE COMPID = 1001

Output:
ORA-02292: integrity constraint (P65593220.SYS_C008934133) violated - child record found

Quiz 70: Consider the 'Student' table as :

UPDATE Student SET DOJ = '01-FEB-15', Gender = 'F' WHERE Gender = 'M' AND Name = 'Alan'

UPDATE Student SET Marks = Marks-10, Name = 'Alice' WHERE Marks > 70 AND DOJ < '31-JAN-2015'

Which of the following 2 statements will be true after the execution of the above Update queries in that order?

a) All Students have different names
b) There is only 1 Male student ---
c) Exactly 2 Students will have marks greater than 80 ---
d)Only 1 student has DOJ in February month

Quiz 71: Which of the following columns in a table cannot be updated?

a) DATE type columns in the table
b) Columns which allows NULL values in the table
c) A primary key column which also serves as a foreign key reference in another table ---
d) All of the above

-
-

Exercise 18: UpdateUpdate discount of all products whose category is 'Sports' to 25 percent.

Exercise 19: Update

Update price of products to 50 whose category is 'Apparel' and product description is 'Trouser'.

Exercise 20: UpdateUpdate the Salesman name to 'Jenny' and location to 'Bristol' for the salesman having SID as 3.