|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
|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.|