Manipulating Data Using Insert, Update, and Delete in SQL Server
Introduction
Before you can aspire to make data-driven decisions, it is imperative that such data is stored somewhere and is accessible by you. Furthermore, you will want to ensure that it is updated when required or discarded when no longer needed.
This guide explains how to insert, update, and delete records from tables using SQL statements. Although we will utilize the MySQL database from Querying Data with SQL (DQL), the examples outlined here will work in Microsoft SQL Server as well.
In practice, data is inserted, updated, and deleted from tables using a wide variety of methods. These range from manual operations to external applications and everything in between. Regardless of the method, under the hood, it all boils down to SQL queries being executed either by a person or automatically in response to an event. For simplicity, we will use manual entry throughout this guide but the same statements apply in other scenarios.
Inserting Data
The syntax for inserting a new record to a table is very straightforward:
1INSERT INTO table_name (field1, field2, ...)
2VALUES (value1, value2, ...);Where
field1 and field2 are fields from table_name.
- value1 and value2 are the values for field1 and field2, respectively. SQL gives you the flexibility to list the fields in the order that you wish, as long as you specify the corresponding values accordingly. Thus, the following code is equivalent to the above query:
1INSERT INTO table_name (field2, field1, ...)
2VALUES (value2, value1, ...);It is worth noting that the data type and length should match the table declaration. At best, boolean values (0 or 1) may fit into a field declared as an integer but not necessarily the other way around. At worst, attempting to enter 100-character strings in VARCHAR(50) fields will simply end in an error or in data getting truncated. Thus, it is best to be as accurate as possible in the data type declaration and its use.
- The ellipsis (...) is not part of the SQL code. It indicates that other fields and their corresponding values may also be included in the statement.
A variation of INSERT allows several comma-separated records to be inserted at once as follows:
1INSERT INTO table_name (field1, field2, ...)
2VALUES (value3, value4, ...),
3(value5, value6, ...),
4(value7, value8, ...);Fig. 1 illustrates this approach (commonly referred to as bulk insert), which was used to populate the customers table in the previous guide. Since customer_id is an auto increment column, it is populated automatically upon each insert.
Auto increment fields are better known as identity columns in SQL Server. They are declared using the
IDENTITYproperty:customer_id INT IDENTITY(1,1)where(1,1)indicates that it will start at 1 using steps of the same value.
Additionally, you can also insert rows to a table using the results of a SELECT query. For instance,
1INSERT INTO table_name (field1, field2, ...)
2SELECT fieldX, fieldY FROM other_table;will insert all values from fieldX and fieldY in other_table to table_name. As you may well expect, the SELECT can be simple as above or complex, involving two or more tables.
To illustrate, let us add a new book called Kicking In the Wall written by Barbara Abercrombie and published by Harper Collins to our library.
1INSERT INTO books
2(book_name, book_isbn, book_edition, author_id, publisher_id)
3SELECT
4'Kicking in the wall' AS book_name, '9781608681563' AS book_isbn,
51 AS book_edition, a.author_id, p.publisher_id
6FROM authors AS a, publishers p
7WHERE a.author_name = 'Barbara Abercrombie'
8AND p.publisher_name = 'Harper Collins'; Fig. 2 shows the output of the SELECT on its own before performing the actual insertion:
The above example also highlights that you can select fixed values (book_name, book_isbn, and book_edition) along with values from the database (author_id and publisher_id) in the same query.
Now that we have learned how to insert data to a table, let us explore how to update or delete the information that is already available in the database.
A word of caution before you proceed. In the following two sections we will learn how to update and delete existing data. In any event, be careful because you will be modifying the content of one or more tables. Depending on the number of affected rows, the only way to reverse the change may be restoring a recent backup!
Updating Data
Whenever you need to change the value of certain fields in one or more rows, you will come across the UPDATE statement. In its most simple form, the syntax is the following:
1UPDATE table_name
2SET field1 = X,
3field2 = Y
4WHERE field1 = Z;Where field1 and field2 are two fields from table_name whose values will be changed to X and Y, respectively - but only on the record where the current value of field1 is Z.
You can add as many field = value pairs as needed, as long as they are separated by commas.
If you omit the WHERE clause, those fields will be updated for all the records in the table. To minimize the likelihood of human error during this operation, you can follow a simple rule of thumb: do a SELECT first. If you utilize the same WHERE clause and it returns the expected row(s), you can go ahead with the UPDATE.
For example, let's say we need to correct customer Jill Devera's name to Jack and Jill Devera and the address to 62 Fillmore Ave. To begin, let us execute the query below:
1SELECT * FROM customers WHERE customer_name = 'Jill Devera';As we can see in Fig. 3, we can proceed to update the customer's name and address on file if we are sure it is the right record. To do so, let us begin by deleting everything to the left of the WHERE clause:
1WHERE customer_name = 'Jill Devera';Then add on top:
1UPDATE customers
2SET customer_name = 'Jack and Jill Devera',
3customer_address = '62 Fillmore Ave'
4WHERE customer_name = 'Jill Devera';
Fig. 3 also shows another SELECT after the UPDATE took place that confirms it was successful. Note that since the name was changed, we used customer_id in the WHERE clause.
Deleting Data
Before we attempt to delete any records, it is worthy and well to keep the same precautions as with the update operation. In short, we are only safe to proceed if the SELECT returns the correct row(s) when you apply the same filter condition.
Most of the scenarios where you might want to delete data are already familiar to you. Whenever you unsubscribe from an email newsletter, sell a product (either online or in-person), or donate office furniture, the DELETE statement is likely involved. In the first example, you request that your address be removed from a distribution list. The second and third scenarios assume that you keep track of the products you offer and the office assets under your care using a relational database (as you should!).
To delete the row(s) of table_name where the current value of field1 is Z, do as follows:
1DELETE FROM table_name
2WHERE field1 = Z;Sad news - our customer Tim Murphy has canceled his membership so now we need to remove his name from the customers table. First off, we need to make sure he has returned all the books he ever borrowed from the library:
1SELECT customer_id FROM customers WHERE customer_name = 'Tim Murphy';
2SELECT COUNT(customer_id) FROM loans
3WHERE customer_id = 10;Since the result is 0, as shown in Fig. 4, we can write the DELETE query with confidence using what we have just learned:
1DELETE FROM customers
2WHERE customer_id = 10;
Note that the
WHEREclauses in theSELECTandDELETEqueries are identical.
The fact that we checked the loans table before deleting the customer was not just to avoid losing books. Remember how the customer_id foreign key in loans points to the same field in customers (where it is a primary key)? This is what we call a constraint in SQL, and its purpose is to prevent data integrity issues, which is what would have occurred if we had a customer_id = 10 in loans but not in customers.
Summary
In this guide, we reviewed the fundamental concepts of data insert, update, and delete in flavor-independent SQL. You can use it as a reference guide or as a cheat sheet whenever you find yourself in the need to work with data either directly or through an interface application.