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

From Script | Spoken-Tutorial
Jump to: navigation, search
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