RDBMS-PostgreSQL/C2/Updating-Data/English

Visual Cue	Narration
Slide 1: Updating Data	Welcome to the Spoken Tutorial on Updating Data
Slide 2: Learning Objectives Update statement Delete statement	In this tutorial we will learn about Update statement and Delete statement
Slide 3: System requirement	To record this tutorial, I am using Ubuntu Linux 14.04 operating system PostgreSQL 9.3.x pgAdmin 1.18
Slide 4: Pre-requisites	To follow this tutorial, you should have Basic understanding of SELECT statement. For more details, refer to the RDBMS PostgreSQL series on this website.
Slide 5: UPDATE	The update query is used to modify the existing records in a table. We have to use the WHERE clause with the UPDATE statement to update the selected rows.
Slide 6: Syntax : Update The syntax for update statement is: UPDATE table-name SET column1 = value1, column2 = value2 , ... [WHERE condition] Highlight according to narration	The syntax for update statement is shown here. The columns to be modified are to be specified in the SET clause. Column1 would be assigned the value1 and Column2 would be assigned the value2 and so on. Other columns in the table will retain their previous values. WHERE clause is used to find the exact row based on the specified condition, to update it. If no conditions are provided, then all records in the table will be updated.
	Let's switch to pgAdmin main screen.
Right click on students node>> View data >>View All rows.	Now right-click on students node, then on View data and then on View All rows.
Point to the row	Notice here - it says the student whose id is 's008' is from 'Chennai' city. Now I want to change the city to 'Bangalore' for this student.
Switch to the SQL editor window	Now switch to the SQL editor window and type the update statement, as shown here.
Open the SQL editor window and type the code. Update students set city=' Bangalore' where studentid = 's008'	This is a simple update statement which updates a single column.
Highlight according to narration.	The existing city value 'Chennai' will be changed to 'Bangalore' for the studentid s008'.
Point to the message	Execute the query. We can see a message that the query is successful.
	Let's retrieve the record to see the changes.
Clear the editor and type the query. Select * from students where studentid='s008'	Clear the editor window and type this select statement.
Point to the output.	Now, execute the query to see the output. Note the updated value in the city field for student ‘s008’.
	Next we will see how to update multiple columns.
Type the code Update students set dob=' 1996-11-21', city ='Chennai' where studentname ='Ram charan'	Type the code. We can also update multiple columns by separating the column and value pairs, with commas, as shown here. Remember, the dob column was null for the student 'Ram charan'. This will update two columns, dob and city, with the values given in the update statement.
Highlight the where condition	What will happen if you forget to give the where condition? The entire students table will be updated with the given value.
Click on execute button	Execute the query.
	Let us see the updated value for the student name ‘Ram Charan’
Select * from students where studentname ='Ram charan'	Type the select statement as shown here.
Point to the output.	Execute the query. We can see the date of birth column and city column has been updated.
	Next we will learn about the delete statement.
Slide 7 : DELETE	The syntax for delete statement is as follows: DELETE FROM table-name [WHERE condition] WHERE clause is used in the delete statement to delete specific rows which satisfy a particular condition. All records will be deleted if no conditions are specified.
	Let us see an example of the delete statement.
	Switch to the SQL Editor window.
Type the code Delete from students where studentid ='s014'	Type the code as shown here. This statement will delete the student record whose id is 's014'.
	Let us execute the query. So one row is deleted now.
Delete from students	If you want to remove all the rows from the students table, type delete from students. There is no Where condition is specified here. I'll not execute the query, as I don’t wish to delete all the record.
	Next let us see an example where we have two conditions in the DELETE statement.
Delete from students where deptid='CS' and cgpa < 7.5	Type the code as shown here.
Highlight the condition	This delete statement will delete the student details which satisfies two different conditions. That is, the department should be 'CS' and the CGPA score should be less than 7.5
Click on execute button	Let us execute this query.
Point to the output	In the output, we can see ‘0 rows affected’. This indicates that no records are deleted. That means there are no records in the student table which satisfies both the conditions.
	With this we have covered the basics of common SQL statements such as INSERT SELECT UPDATE and DELETE in this series.
Slide 8(a): DML statements DML is short form of Data Manipulation Language. It includes commands that are used to manipulate data in a database.	These statements are otherwise called as Data Manipulation language. DML is the short form of Data Manipulation Language. It includes commands that are used to manipulate data in a database.
	With this we come to the end of this tutorial. Let us summarize.
Slide 9 Summary	In this tutorial, we have learnt about the - Update statement and Delete statement
Slide 10 Assignment	As an assignment, For studentid 's013', modify the student name from 'Sharmila' to 'Sharmila Babu' in the students table Delete the students whose cgpa is less than 5
Slide 11: About Spoken Tutorial project	The video at the following link summarises the Spoken Tutorial project. Please download and watch it.
Slide 12: Spoken Tutorial workshops	The Spoken Tutorial Project Team conducts workshops gives certificates For more details, please write to us.
Slide 13: Forum for specific questions	Please post your timed queries in this forum.
Slide 14: Acknowledgement	Spoken Tutorial project is funded by NMEICT, MHRD, Government of India. More information on this mission is available at this link.
	This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

Contributors and Content Editors

Nancyvarkey, Nirmala Venkat