In this discussion, we’ll explore the update statement and take look at the where clause. The Update statement is used for updating values in a cell/cells of a table.
It is commonly used on tables that already exist in the database , temporary tables or table variables. We’ll take look at table variables later in the tutorial.
So lets take a quick tour of a table student and see how we can use the update statement to change values in some of the columns. There is a big caution we have to take when using the UPDATE clause, if used without a WHERE clause, all the values for the column specified will be changed to the new value.
So go over the student table and see what l’m talking about.
–creating the table
CREATE TABLE student(sid int primary, firstname varchar(20), lastname varchar(20))
–inserting records into the table
INSERT INTO student
VALUES (1,’Cosby’,’Amedekah’),(2,’Vincent’,’Amedekah’)
–Updating the first name of the student with sid of 1 to kofi
UPDATE student
SET firstname = ‘kofi’
WHERE sid = 1
Now, if you didn’t specify the WHERE CLAUSE, all students first name will be changed to kofi. This is why care has to be taken when updating and deleting. To be frank, when using DDL statements, care have to be taken to protect the data or structure you are working with.
The WHERE CLAUSE goes with delete, select and update.
For example, lets display the information of the student whose sid is 2
SELECT * FROM student
WHERE sid = 2
Lets delete the student whose sid is 1
DELETE From Student
WHERE sid = 1
If the WHERE CLAUSE is not stated, all records will be deleted and the table left empty.
You can’t use the WHERE CLAUSE with DROP and TRUNCATE.
In the next tutorial, we’ll look at how to use Wild cards in performing various types of searches in the database.
Questions and Feedbacks are welcome, iust drop them in the Comment boxes below.
