Let us consider the same library database to illustrate update, delete, like and Top clauses.
Update statements are used to update existing records in the table. It modifies the existing record values in the table.
Consider we need to modify the address of a borrower – Rose
UPDATE BORROWER SET ADDRESS = ‘PO BOX No 201 Kerala’;
Will above update statement update Rose’s address? No. Above UPDATE statement is similar to ‘SELECT * FROM table’ query. This update statement pulls all the records from BORROWER and updates their records to new address. It does not get any information that it has to update Rose’s record. Hence above query needs to pull Rose’s record and then update her record. This is done by using WHERE condition. i.e.;
UPDATE BORROWER SET ADDRESS = ‘PO BOX No 201 Kerala’ WHERE BRWR_NAME = ‘Rose’;
Suppose we have to update address and phone number of Kathy. Here we have to update two columns. Both of them can be updated using single UPDATE statement as below.
UPDATE BORROWER SET ADDRESS = ‘PO BOX No 34 Bombay’, PHONE = 9880988123 WHERE BRWR_NAME = ‘Kathy’;
Hence using single update statement we can update all the columns in a table. We can even update primary key column of a table provided the new value being updated is unique.
UPDATE BORROWER SET BRWR_ID = 'JSC_1212' WHERE BRWR_ID = 'JSC_1004';