Here we go.

In here we will update this record.

The UPDATE statement would allow us to do that.

It is used to update the values of existing records in a table.

The syntax to adhere to is UPDATE table name, the keyword SET, column names and the respective

values assigned to them, and finally – WHERE, and a certain condition, or set of conditions,

that must be satisfied.

By using this code structure, SQL will change the record or records satisfying the WHERE

condition, updating the old values of the columns listed with the new ones.

Ok!

We can see that employee number 9-9-9-9-0-1 is John Smith.

So, we can create a query with the following update statement:

UPDATE “Employees”, SET, and then assign the value of the string “Stella” to the

“first name” column, the string “Parkinson” to the “last name” column, the 31st of

December 1990 to “birth date”, and “F” to “gender”.

The condition here is to have an employee number that equals John Smith’s number,

9-9-9-9-0-1.

This means SQL’s optimizer will, so to speak, access the record with employee number 9-9-9-9-0-1,

and substitute the existing values with the new ones indicated in the UPDATE statement.

Note that we did not update the “hire date” column value, right?

Moreover, SQL showed no error because of that.

This is fine, as we do not have to update each value of the record of interest.

Of course, we can still say we have updated the specific record!

Ok, we can finally run this query.

After that, we will select the same employee through the employee number once again.

Let’s see what happens.

We see Stella Parkinson there, not John Smith!

With a different birthdate and gender, although with the same hire date.

Great!

Please note the following feature of the UPDATE statement.

Had we used a non-existent condition in the WHERE clause (for instance, an employee number

of 9-9-9-9-0-9), MySQL would have allowed the execution of the query, given that the

SQL syntax is correct.

Nevertheless, nothing would have happened – the statement would have worked, affecting

0 rows, because the data table doesn’t contain an employee with such a number at the moment

of the query’s execution.

Awesome!

When updating your table, the WHERE clause is crucial, although by default in MySQL it

is set to be optional.

If you don’t provide a WHERE condition, all rows of the table will be updated.

Check what we have in the “Departments duplicate” table – nine rows and two columns with information.

Good.

For the sake of this exercise, we will change all the department numbers and names in this

table.

Right before that, we will execute a COMMIT command.

It will “save” the data set as we see it.

Now, if we write the code that updates the “Department Duplicate” table, setting

a department number equal to D-0-1-1 and a department name “Quality Control”, and

then run it, we will modify all rows of the data table.

Is this true?

Let’s see…

Absolutely!

“Departments Duplicate” is a table now comprising identical rows!

This would typically happen by mistake – when the WHERE clause and the accompanying conditions

have not been added.

If we’ve done this by accident, is there a way to go back?

Of course!

We can do that by applying the ROLLBACK command!

When executed, it will take us to the last COMMIT that has been run.

Or, if we have not run a COMMIT command beforehand, it will take us to the beginning of the entire

SQL code, and we will potentially lose hours of work.

That’s why it was important to COMMIT the changes we made a minute ago.

Remember, you should be careful when using this command because once you execute COMMIT,

you cannot reverse any change!

So, after running ROLLBACK, where will we go back to?

Let’s check…

Perfect!

We see the information we had in the “Departments Duplicate” table at the beginning of this

lecture. ?