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

In this lesson, we'll be taking
a look at the update statement.

So this is the
DML statement, DML

being a sub-language of SQL.

Update is the
statement we use when

we want to modify
existing data in a table.

So let's go ahead and open a
connection to our database.

We'll use the scott_orcl.

We'll open this table list again
just to have it in front of us.

And actually, in this case,
let's open the bonus as well.

So this is going to
show us the columns that

are in the bonus table.

And it gives us data types
and various other pieces

of information.

We click this tab come back
and do our update statement.

So let's type the statement in.

And then, we'll explain and
break down what it means.

So the format of
an update statement

is to use the update
clause then the table name

that you're updating.

So this is the table that
has the data we'll update.

Set the column name that we
want to change to this value

where some row is returned.

So having this
where clause enables

us to pinpoint the exact
row that we want to change.

So "set" is showing us
the column to change.

And "where" is
showing us the row.

So before we execute this,
let's look at the existing

state of the data.

Select star from bonus.

All right.

Here is the row
that's our target.

Sam's job currently is clerk.

We want to change this.

We want to update this table
and set his job to analyst.

We're going to click up
here in this row, execute.

It says one row was updated.

And now, we'll look
at the table again.

Notice now that
Sam is an analyst.

So we've updated
that specific row.

So what if we didn't
have this where clause?

Well, that's an important
thing to point out.

Because without this
limiting condition,

the job column would be set
to analyst for every row.

And that's probably not
what we want in this case.

So remember that we always
need to not only set

the column for a
new value, but we

need to use a where clause
as a limiting condition

to only bring back
the rows that we want.

Now, let's try updating
a couple of columns.

We'll set job to manager and
sal equal to where ename is Sue.

All right.

So what we've done
here is, we've

updated two column values.

So we've set Sue's job to
manager and her sal to 1700.

And we click Run
to see the table.

And we notice now that Sue is
a manager with a sal of 1700.

So what we've done
is, we've updated

two columns in that row.

Now, our where clause can
allow for more than one

row to be updated.

Here, this limiting
condition brings back

just the row for Sue.

But we could use a different
limiting condition.

For instance, now,
let's try this.

Let's set COMM--
that's this column--

equal to 3 where
sal equals 1700.

So based on the data
that we have here,

there are two rows that have
a value of 1700 for the sal

column.

So if we set this COMM
to 3, both of these

should be changed to 3.

And just to make it clear,
we'll set them to 1.

We click Run.

And it says two
rows were updated.

Click in the select statement.

And we see, indeed, that the
column was set to one or both.