Placeholder Image

字幕表 動画を再生する

  • All right!

  • 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 finallyWHERE, 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:

  • UPDATEEmployees”, SET, and then assign the value of the stringStellato the

  • first namecolumn, the stringParkinsonto thelast namecolumn, the 31st of

  • December 1990 tobirth date”, and “F” togender”.

  • 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 thehire datecolumn 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 happenedthe 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 theDepartments duplicatetablenine 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 willsavethe data set as we see it.

  • Now, if we write the code that updates theDepartment Duplicatetable, setting

  • a department number equal to D-0-1-1 and a department nameQuality Control”, and

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

  • Is this true?

  • Let’s see

  • Absolutely!

  • Departments Duplicateis a table now comprising identical rows!

  • This would typically happen by mistakewhen the WHERE clause and the accompanying conditions

  • have not been added.

  • If weve 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 theDepartments Duplicatetable at the beginning of this

  • lecture. ?

All right!

字幕と単語

ワンタップで英和辞典検索 単語をクリックすると、意味が表示されます

B1 中級

SQLチュートリアル|実データを使ったSQL更新文の使い方を学ぶ (SQL Tutorial | Learn How to use the SQL Update Statement with Real Data)

  • 0 0
    林宜悉 に公開 2021 年 01 月 14 日
動画の中の単語