DML Statements to DELETE and UPDATE Data

Using DML Statements to DELETE and UPDATE Data

Learning Materials

The SQL DELETE and UPDATE commands are Data Manipulation Language commands. Data Manipulation Language (DML) is a set of SQL statements that are used to add, modify, query, or remove data from a SQL Server database. The SQL DELETE statement allows you to delete a single record or multiple records from a table. The DELETE statement can delete all records in a table. Also, it can be used to remove data from a SQL view. It can be constructed to delete data on specific columns. The SQL UPDATE statement is used to update a single record or multiple records in a table. When UPDATE command is issued, all the rows can be updated, or a subset may be chosen using a condition. One thing that is of note is that the SQL UPDATE command can update records for a single table only. It can’t be used like the SELECT statement to search multiple tables. Like the delete statement, it can also be used to update a view.

DELETE

Generally, clauses are used to specify the condition or criteria for deleting records. For example, the FROM and WHERE clauses are used to specify the table and the condition. The clauses search for the record that meets the condition that a query will use to delete data. If a WHERE clause is not specified, all the rows in table will be deleted. The FROM clause specifies additional tables or views and JOIN conditions that can be used by the predicates in the WHERE clause search condition to qualify the rows to be deleted from a table.

The syntax is shown below:

DELETE FROM table_name
WHERE some_column= some_value 

The WHERE clause in the syntax specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted.

For example, the command shown below will delete data from a table named CUSTOMER:

DELETE FROM CUSTOMER
WHERE ID = 102

The result of a SELECT * statement after a DELETE statement was executed for 102 is shown below:

ID            Name                    Age        City                        Salary
101         Johnson               35           Atlanta                   2800.00
103         Perez                     30           Miami                    3500.00
104         Gucci                     40           New York              1200.00
105         Patel                      38           Chicago                3000.00

Another Example

We can use another column as criterion for deleting a record as shown below:

DELETE from CUSTOMER
WHERE Name = ‘Perez’

The result of a SELECT * statement after a DELETE statement was executed for Perez immediately after 102 was deleted is shown below:

ID            Name                    Age        City                        Salary
101         Johnson               35           Atlanta                   2800.00
104         Gucci                     40           New York              1200.00
105         Patel                      38           Chicago                3000.00

UPDATE

Clauses are used to specify the condition or criteria for updating records. For example, the FROM and WHERE clauses are used to specify the table, the column, and the condition. The clauses search for the record that meets the condition that a query will use to update records or data. The syntax for updating a table is shown below:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition]

Update Command Example

The command below is used to update the City column in the CUSTOMER table:

UPDATE CUSTOMER
SET ADDRESS = 'Dallas'
WHERE ID = 104

The result of a SELECT * statement after an UPDATE statement was executed on the CUSTOMER table is shown below:

ID            Name                    Age        City                        Salary
101         Johnson               35           Atlanta                  2800.00
102         Smith                    25           Boston                  1500.00
103         Perez                    30           Miami                     3500.00
104         Gucci                    40           Dallas                    1200.00
105         Patel                     38           Chicago                 3000.00

To change both City and Salary, the following command can be issued:

UPDATE CUSTOMER
SET City = 'Dallas', SALARY = 4000.00
WHERE ID = 104

Summary

The SQL DELETE statement allows you to delete a single record or multiple records from a table. The DELETE statement can delete all records in a table. Also, it can be used to remove data from a SQL view. The SQL UPDATE statement is used to update a single record or multiple records in a table. When the UPDATE command is issued, all the rows can be updated, or a subset may be chosen using a condition. Both DELETE and UPDATE are data manipulation language (DML) commands.

hihi


Want latest solution of this assignment

Want to order fresh copy of the Sample Template Answers? online or do you need the old solutions for Sample Template, contact our customer support or talk to us to get the answers of it.