Data Manipulation in SQL Tutorial

Welcome to lesson nine ‘Manipulating your Data’ of the SQL Tutorial, which is a part of the SQL Training Course. In this lesson, we will talk about the INSERT, UPDATE, and DELETE statements of SQL.

Objectives

By the end of this lesson, you will be able to:

  • Explain the INSERT statement and how it can be used

  • Describe the UPDATE query with examples

  • Explain the DELETE statement with example

The INSERT Statement

The INSERT statement is one of the three statements belonging to the so-called “data manipulation language” part of the SQL - that is the INSERT, UPDATE, and DELETE.

All three statements allow you to change data in the database, not changing the structure but changing the content. They are different from the SELECT statement, which only allows you to read the data from the database.

So the INSERT statement allows you to add new records to your database table. Generally, it is used to add records at the end of the table. One thing which is important whenever you do INSERT is that your data needs to satisfy all the rules in your database.

Consider the image shown below:

First, we look at the Person table as an example. Here we have the primary key. The primary keys in that database are previously set to auto increments in our SQL.

We never insert the primary key ourselves, we leave that to the database. Then we have a firstname and lastname, which have NOT NULL flag. The solid diamond indicates that these fields are mandatory, we can't skip them.

Last is the timestamp, which is NOT NULL, but at the same time, the default value is the current timestamp. So again, that would automatically set a current timestamp if we don't put any date in there.

If we need to insert records in another table, we need to make sure that the data actually exists. So in these kinds of the end to end tables or on any kind of foreign key situation, you always need to make sure that the data you insert there actually represent valid values in the referred tables.

Let us use our Person table again as a starting point.

Person table at the start is as shown.

Consider an example shown below:

select * from person

insert into person

values (7, ‘Martin’, ‘Holzke’, ‘1980-05-05’, ‘xxx’, now());

We can only insert into one table at a time. You can join data together using the SELECT function, but traditionally speaking, you can do data manipulation on one table at a time.

What we've considered in the above example is the ‘implicit insert.’ It means that we're not mentioning the columns that we want to populate, we need to put them all in exactly the order in which the table exists.

The insert will match the new values to the table columns automatically.

Note: For the insert, update, and delete statement, we need to keep in mind that the databases don't have an undo button. So once you've sent any values, it gets stored into the database.

Since we are learning about the implicit insert, we need to specify the values for each column. So seeing the database table in the same screen helps immensely.

Now, if we run the above code, you can see that the table has been updated and the record inserted can be seen in position 7.

Also, the insert statement doesn't return anything, in contrast to the SELECT statement. This is why the screen goes blank afterward.

Next. let’s see the explicit version, which is highly recommended to use.

select * from person

insert into person (firstname, lastname, managerid, dob)

values (‘Martin’, ‘Holzke’, 5, ‘1980-05-05’);

In the above example, we have just mentioned 4 values of the table. The rest gets default populated. When we run the query, we see that the new values have been added to the table.

The primary key value is 9 and not 8 because that value because it was used at some point in the database already.

This is advantageous for security purpose since the primary key cannot be reused and errors can be identified.

We also see that the ‘created’ column value is set with the current timestamp when we explicitly do not mention it.

Next, to do multiple insertions, we can run the above query, again and again, using different values.

The multiple insertions can be performed using one insert statement only.  We add the new set of values that are comma separated.

select * from person

insert into person (firstname, lastname, managerid, dob)

values (‘Martin’, ‘Holzke’, 5, ‘1980-05-05’),

(‘Fred’, ‘Flintstone’, 5, ‘1987-06-02’);

The following image shows the updated table when multiple insertions are done.

Next, we want to do an insert by selecting data from the same table, so you can copy data which proves quite handy for populating a database. For example, in data migration, you actually want to copy data within a database or between different databases/tables.

The following code shows how to copy data.

select * from person

insert into person (firstname, lastname, managerid, dob)

select concat(‘copy of’, firstname), lastname, managerid, dob

from person

where id>=10

The following image shows the values of the table when we only run the select statement.

Now, we run the query along with the insert statement. This can be done just by selecting the statements that we wish to run and execute that query.

The following image shows how the table has been modified after running the above query.

Hence, we saw how copying values using insert statement can populate the database. We generally populate the database when we run the queries in a test environment.

SQL - Update Query

The update query or statement is the second out of the three data manipulation language statements, which are INSERT, UPDATE and DELETE. So the UPDATE query allows you to modify existing records in a table.

Since we're talking of the SQL as a set-based language, the UPDATE query will work on a set of records and not on one record (depending on how you run your UPDATE).

So how do we do all our updates?

Consider the query given below:

select * from person

where id = 10

update person

set dob = ‘1990-01-01’

where id = 10

In the above query, we first select the records from Person table whose id equals 10. The following image shows the value which gets selected.

Then we use the Update query to set the DOB for the record whose id is 10. If we do not use the where statement, it will update every single record in our table to the set value.

On executing the above query, we see that the dob value for the 10th entry in the table has been updated.

Now, to update multiple things in a record, we can use the comma-separated list.

Consider the query shown below:

select * from person

where id = 10

update person

set dob = ‘1990-01-01’, firstname = ‘Mike’

where id = 10

On executing the above query, we get the updated table as shown:

Next, if we want to change more than one record, we can do the following:

select * from person

where firstname = ‘Martin’

update person

set firstname = ‘Mike’

where firstname = ‘Martin’

In the above query, we have changed the firstname value from Martin to Mike, wherever it previously existed in the Person table.

Let us consider a different example:

The person table did not have any scope for doing a bit more numerical stuff, so let us consider the project table.

Here, we try to take a scenario where a handling on the whole table might be desirable.

The project table has three records at the moment, as shown.

Each record has a budget value. Let us say that we want to upgrade them all by about 20%. We can do this using the query shown below:

select * from project

update project

set budget = budget*1.2

So the above query would now increase all of the records in the project table by twenty percent.

The updated records are as shown below:

Hence, many mathematical operations can be performed on the values as in the example shown above.

One thing to remember while using the UPDATE statement is to use the ‘where’ clause wherever applicable. Otherwise, it will update the values of all the entries in the table.

SQL DELETE Statement

Let's study the DELETE statement now, which is the last statement of the three data manipulation language statements. The DELETE statement has the ability to delete one or more records in full. We're not talking about deleting individual column content because we can't do that.

If you want to do that, you need to use the UPDATE statement to change the content of the individual columns. So DELETE is the deletion of a full record or multiple full records.

As applicable with INSERT and UPDATE statement, once you delete a series of records, they're gone and there's no way of getting them back.

We also need to make sure that when we delete any record from the table, that entry (or its value) is not being used by any other table.

The person table has the following initial entries.

The DELETE statement can be used as shown in the following query:

select * from person

where id = 10

delete from person

where id = 10

In the query shown above, we have deleted the record whose id value was 10.

Note: Remember to use the ‘where’ clause with the DELETE statement. Otherwise, the statement will quickly empty your person table without the ‘where’clause.

When we execute the above query, we find that entry with id value 10 has been deleted and the updated table is as shown. Everything else remains, except for the one with a primary key ten.

There are situations where we want to get rid of a whole block. For example, we wish to delete all the copy entries from the person table as shown.

The query to delete all the copy records from the person table is shown below:

select * from person

where firstname like ‘copy%’

delete from person

where firstname like ‘copy%’

In the above query, ‘copy%’ indicates the statements that start with copy and continues with any word further.

On executing the above query, we can see that all the records that started with ‘Copy’ have been deleted from the person table.

Note: It is always recommended to use the DELETE statement for one table at a time. It is not recommended to use the statement in the case of joint tables.

Conclusion

With this, we have come to an end of this lesson on ‘Data Manipulation in SQL.’ The next lesson focuses on Transaction Control.

  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

Request more information

For individuals
For business
Name*
Email*
Phone Number*
Your Message (Optional)
We are looking into your query.
Our consultants will get in touch with you soon.

A Simplilearn representative will get back to you in one business day.

First Name*
Last Name*
Email*
Phone Number*
Company*
Job Title*