All language subtitles for [SubtitleTools.com] Deletes - Learning Oracle 12c [Video]

In this lesson, we're going to
be taking a look at the DELETE

statement.

So the DELETE statement
is the command

that we use in
SQL to remove data

or to remove a row or rows
of data from a given table.

So let's connect, and
look at our table list,

and start out by selecting
star from the bonus table.

Run.

And this is the data that
we have to work with.

So let's type in a DELETE
statement and then dissect it.

So when we run a
DELETE statement,

we'll use the DELETE
keyword and the FROM

keyword, much as the same
as a SELECT, and then

the table name, so we
delete from the table name.

And then we have a limiting
condition where our E

name is Sam, equal to Sam.

So we're targeting
this row right here

with our DELETE statement.

We click Run.

It tells us one row deleted.

And we'll select from the table.

And we see that the Sam row has
been deleted from the table.

Now we can also
illustrate the importance

of the limiting condition.

What if we were to
type delete from bonus?

Now we look at the bonus table,
and there's no rows left.

Now, this was a small table,
but what if this had been

a million-row production table?

Then this is a serious problem.

And it happens more often
than you might think.

So we just deleted all
of our customer data

from the bonus table, or
maybe our employee data.

And that's a bad thing.

So what do we do
to get that back?

Is there some kind of database
recovery involved, or what?

Well, that's the importance
of a transaction.

So in Oracle and in
relational databases,

commands are centered
around transactions.

A transaction isn't finished
until a commit actually occurs.

Now we can use the
commands commit,

but we don't want to
commit in this case.

Because let's say by doing
this DELETE statement,

we've actually made a mistake.

We want it to come back.

So it's not real, truly
processed in the database,

until we do a commit.

Even though when we
select it, we see it,

we're not seeing the
actual image of the data

at the end of the transaction.

So we want to use a
command called rollback.

When we execute rollback,
it says rollback complete.

And now we select star from
bonus, and we've rolled back.

But look-- not only do
we have Fred and Sue,

but we also have Sam.

So Sam was our first statement,
and then the second statement

of DELETE--

from bonus, the entire
table deleted all the rows.

And yet since no commit occurred
after those two statements were

issued, it rolls
back all of them.

So this is the importance
of commit and roll back.

So this time, let's delete
from the bonus table.

And we'll use a commit.

So we say delete from bonus.

The previous example
used an E name

of Sam, which
deleted only one row.

Let's say we want to
delete multiple rows.

We say where E name equals
Sue and comm equals 2.

I'll change that to or.

All right.

So this DELETE statement will
delete from the bonus table

where one of two things occurs--

if the E name is Sue,
it will delete the row,

or if the value for comm is
2, it will delete the row.

Execute.

It tells us two
rows were deleted.

We select star from
bonus, and those are gone.

Now, at this point,
we could do a rollback

and bring those values
back or those rows back.

But instead, we'll do a commit.

Execute.

And now when we do a select,
we see again only one row.

What if we try a
rollback at this point?

And we select.

That row-- those rows,
rather, are still gone.

Because once we
executed a commit,

that made the
transaction committed

in the database and real.

And you can't roll
back after a commit.

Now any statements that
we had between the commit

and the rollback would
be rollback, but not

the transactions
prior to the commit.

And I want to point
out two things here

about commit and rollback.

First of all is there
is a Commit button

on the toolbar here for
SQL Developer and also

a Rollback button.

The last thing is
that whenever we

use a tool to connect
to a database,

we have to be aware
that some tools are

designed to do an auto commit.

So if this tool,
SQL Developer, had

been set up with auto commit--

and indeed, you can change
that in the preferences,

but it's not by default--

if you had an auto
commit, then every time

you execute a statement--

INSERT, UPDATE, DELETE--
it's automatically committed.

So keep that in mind when you're
using a tool that connects

to the Oracle database.