Modify rows and columns in tables - SQL教程

- [Instructor] In this video, we will learn about UPDATE statement. It's a DML statement. We use this to modify data in a table. Now, this is the syntax for UPDATE statement. UPDATE table_name SET column_name equals to some value. And if you're trying to update multiple columns, then we give column names and assign some values to them, separated by commas. And then the WHERE condition. Just like other DML statements, if we specify the WHERE condition, then it updates only those rows that satisfy that criteria. If we do not give the WHERE condition, it updates all the rows in that particular table. So now let's try to modify one row in emp_tab table. We are trying to update emp_tab table by setting the salary to 8,000 for emp number 7001. First, let's take a look at the table. I give SELECT * FROM emp_tab. Run this. So if you take a look at the first record, that is emp number 7001, he has a salary of 5,000. Now I want to update that salary to 8,000. So I run this. And when I take a look at the table now, his salary has updated to 8,000. Now when we are updating only one row, it's always important to identify the row with the column value that is unique for that specific row. In this case, we are using the emp number as it is unique for every employee. Instead of employee number, if we are trying to find by the name, it might update multiple rows at a time because names cannot be unique for all employees. There might be more people with the same name. So it is very important that we identify the unique value for every row. We can modify multiple columns at a time using subqueries in the SET clause. First, let's take a look at the emp_tab table. So now this is our emp_tab table. And if we observe the first two rows, we will be modifying Clark's manager and salary to King's manager and salary. So here, if we take a look at the UPDATE statement, I'm trying to modify multiple columns in emp_tab table for employee number 7002. That is for Clark. And I want to modify them to the manager and salary of employee number 7001, that is King's manager and salary. So when I run this UPDATE statement, and we go to the table, so now when we take a look at Clark's manager, it's set to the same value as King's manager. And the salary is also set to the same as King's salary. So now we have modified Clark's manager and salary. We can modify rows in same tables or another table as well by using these subqueries. So here, in this example that we've already seen, we are using the same table here, as well as in the subqueries. In the next example, we'll be using two tables, that is old_emp_tab table and emp_tab tables. So first, let's take a look at our old_emp_tab table. SELECT * FROM old_emp_tab table. And I run this. So if we take a look at the first row, King belongs to department number 10. It hasn't been updated for quite some time. So now I would like to update it to the latest department to which he is assigned, which is in emp_tab table. So what I do is I give something like this. UPDATE old_emp_tab table SET department number to. We are fetching the department number for King from the new table and we are assigning it to the department number in the old table. So now when I run this, and now let's take a look at our old_emp_tab table. For King, the department number is now set to 40, which is the latest department to which he belongs currently. Now, if we try to modify a row to a value that does not exist in the parent table, it returns integrity constraint error. For example, if I'm trying to update emp_tab table and I want to set the department number to 500 where the department number is 10, it throws an error. This happens because department number 500 is not there in the ept_tab table, which is the parent table for department in the emp_tab. This is about the UPDATE statement.