Using 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.

Want latest solution of this assignment
AssignmentHippo Features
  • On Time Delivery

    Our motto is deliver assignment on Time. Our Expert writers deliver quality assignments to the students.

  • Plagiarism Free Work

    Get reliable and unique assignments by using our 100% plagiarism-free services.

  • 24 X 7 Live Help

    The experienced team of AssignmentHippo has got your back 24*7. Get connected with our Live Chat support executives to receive instant solutions for your assignment problems.

  • Services For All Subjects

    We can build quality assignments in the subjects you're passionate about. Be it Programming, Engineering, Accounting, Finance and Literature or Law and Marketing we have an expert writer for all.

  • Best Price Guarantee

    Get premium service at a pocket-friendly rate. At AssignmentHippo, we understand the tight budget of students and thus offer our services at highly affordable prices.

Tap to Chat
Get instant assignment help