RDBMS-PostgreSQL/C2/Updating-Data/English-timed

Time	Narration
00:01	Welcome to the Spoken Tutorial on Updating Data.
00:06	In this tutorial, we will learn about Update statement and Delete statement.
00:13	To record this tutorial, I am using: Ubuntu Linux 14.04 operating system,
00:20	PostgreSQL 9.3.x and pgAdmin 1.18
00:28	To follow this tutorial, you should have basic understanding of SELECT statement.
00:35	For more details, refer to the RDBMS PostgreSQL series on this website.
00:41	The update query is used to modify the existing records in a table.
00:47	We have to use the WHERE clause with the UPDATE statement to update the selected rows.
00:53	The syntax for update statement is shown here. The columns to be modified are to be specified in the SET clause.
01:03	Column1 would be assigned the value1 and Column2 would be assigned the value2 and so on.
01:12	Other columns in the table will retain their previous values.
01:17	WHERE clause is used to find the exact row based on the specified condition to update it.
01:24	If no conditions are provided then all records in the table will be updated.
01:30	Let's switch to pgAdmin main screen.
01:34	Now, right-click on students node, then on View data and then on View All rows.
01:43	Notice here - it says the student whose id is 's008', is from 'Chennai' city.
01:51	Now I want to change the city to 'Bangalore' for this student.
01:56	Now, switch to the SQL editor window and type the update statement as shown here.
02:05	This is a simple update statement which updates a single column.
02:10	The existing city value 'Chennai' will be changed to 'Bangalore' for the studentid s008.
02:18	Execute the query. We can see a message that the query is successful.
02:24	Let's retrieve the record to see the changes.
02:28	Clear the editor window and type this select statement.
02:34	Now, execute the query to see the output. Note the updated value in the city field for student ‘s008’.
02:45	Next we will see how to update multiple columns.
02:50	Type the code. We can also update multiple columns by separating the column and value pairs, with commas, as shown here.
03:02	Remember, the date of birth column was null for the student 'Ram charan'.
03:08	This will update two columns, date of birth and city, with the values given in the update statement.
03:16	What will happen if you forget to give the where condition?
03:20	The entire students table will be updated with the given value.
03:25	Execute the query.
03:28	Let us see the updated value for the student name ‘Ram Charan’.
03:33	Type the select statement as shown here.
03:37	Execute the query. We can see the date of birth column and city column have been updated.
03:46	Next we will learn about the delete statement.
03:50	The syntax for delete statement is as follows: DELETE FROM table-name [WHERE condition]
03:59	WHERE clause is used in the delete statement to delete specific rows which satisfy a particular condition.
04:06	All records will be deleted if no conditions are specified.
04:11	Let us see an example of the delete statement.
04:15	Switch to the SQL Editor window.
04:18	Type the code as shown here. This statement will delete the student record whose id is 's014'.
04:28	Let us execute the query. So, one row is deleted now.
04:34	If you want to remove all the rows from the students table, type delete from students.
04:40	There is no Where condition is specified here. I'll not execute the query, as I don’t want to delete all the records.
04:50	Next let us see an example where we have two conditions in the DELETE statement.
04:57	Type the code as shown here.
05:01	This delete statement will delete the student details which satisfies two different conditions. That is, the department should be 'CS' and CGPA score should be less than 7.5
05:15	Let us execute this query.
05:18	In the output, we can see ‘0 rows affected’. This indicates that no records are deleted.
05:27	That means there are no records in the student table which satisfies both the conditions.
05:34	With this we have covered the basics of common SQL statements such as INSERT , SELECT , DELETE and UPDATE in this series.
05:47	These statements are otherwise called as Data Manipulation language. DML is the short form of Data Manipulation Language.
05:58	It includes commands that are used to manipulate data in a database.
06:04	With this we come to the end of this tutorial. Let us summarize.
06:10	In this tutorial, we have learnt about the - Update statement and Delete statement.
06:17	As an assignment, for studentid 's013', modify the student name from 'Sharmila' to 'Sharmila Babu' in the students table.
06:28	Delete the students whose cgpa is less than 5.
06:33	The video at the following link summarizes the Spoken Tutorial project. Please download and watch it.
06:40	The Spoken Tutorial Project team conducts workshops and gives certificates. For more details, please write to us.
06:49	Please post your timed queries in this forum.
06:53	Spoken Tutorial project is funded by NMEICT, MHRD, Government of India. More information on this mission is available at this link.
07:05	This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

Contributors and Content Editors

PoojaMoolya, Sandhya.np14