Wednesday , November 13 2019
Home / SQL / UPDATE and DELETE statement

UPDATE and DELETE statement

UPDATE Statement:

The UPDATE statement is used to update or change existing data in database table or the base table of a view.

Syntax:

UPDATE     table_name

SET   column1=value1, column2=value2

WHERE  condistions;

Here,

UPDATE  statement will be used to change existing value of a table.

table_name   is the name of the table which column values will be updated.

column1  is the name of the column that data will be changed or updated.

value1  is the new value that will be set instead of existing data of the column. So, column1 will be assigned by the value1, column2 will be assigned by the value2 and so on.

conditions  that must be met before updating table data.

Example:

In following data of the STORE table, price of product id 101 is 3 and product id 103 is 5.

  PRD_ID   QUANTITY      PRICE

———- ———- ———————

       101                10                     3

       103                20                    5

If you want to change the price, execute the following UPDATE statement.

UPDATE  STORE

SET price=7;

Now run the query to show updated data.

SELECT * FROM  STORE;

The result of the query is listed below.

PRD_ID   QUANTITY      PRICE

———- ———- ———————-

   101                   10                     7

   103                  20                     7

From the updated data we see that all rows have been changed by price 7.This is the problem that when we execute UPDATE statement without any condition than all rows have been updated or changed by same value.

To overcome this problem, we have to use WHERE clause to specify conditions along with UPDATE statement.

Example:

UPDATE STORE

SET price=6

WHERE prd_id=103;

In the above example, the current price 7 of prd_id 103 is updating by new price 6.

Now commit and query the updated data.

COMMIT;

SELECT * FROM STORE;

The query result set is below.

PRD_ID   QUANTITY      PRICE

——————- ———- ———-

       101              10                    7

       103              20                   6

In the above result set, we see that the price of  prd_id  103 has been updated by 6 instead of 7. Now the price has been updated for product id 103.

In the above example, we have update only one column data. But if we need to update multiple columns data, than follow the following example.

Here are the current records.

PRD_ID   QUANTITY      PRICE

——————- ———- ———-

       101               10                     7

       103               20                     6

Now we want to change the data of two columns, QUANTITY and PRICE. Execute the statement.

UPDATE STORE

SET price=3, quantity=16

WHERE prd_id=101;

Now commit and query data.

COMMIT;

SELECT * FROM STORE;

The updated records are-

PRD_ID   QUANTITY      PRICE

——– ——————– ———-

      101                  16                    3

       103                 20                   6

In the above updated results, we see that the data of QUANTITY and PRICE column of product id 101 have been updated by 16 and 3 instead of 10 and 7 respectively.

This is how; we can update existing records of a database table or base table of a view.

Note:  When we update multiple column values by a single UPDATE statement, then we have to use comma (,) to separate the columns. We have to also remember that after last column, no comma is required.

DELETE  statement:

The DELETE statement is used to delete rows or records from database table. The DELETE statement will delete the entire row.

Syntax:

DELETE FROM table_name

WHERE conditions;

table_name      is the name of table in database  from which rows will be deleted.

conditions        that should be met to be deleted rows.

Example:

DELETE FROM PRODUCT;

In the example, when you execute the above statement, all rows will be deleted from PRODUCT table. If you want to delete all rows from the table by a single DELETE statement, then you can execute the above DELETE statement without setting conditions.

Now it is time to know, how to delete a specific row or rows from a table.

In maximum times we need to delete specific rows from database table. You can use WHERE clause to set conditions along with DELETE statement to delete specific rows.

Example:

DELETE FROM PRODUCT

WHERE product_id=102;

After executing the above statement, the row of product id 102 will be deleted from PRODUCT table.

Check Also

Unique Constraint

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint: The UNIQUE constraint is used to ensure unique or distinct values …

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

2 comments

  1. Thank you for the sensible critique. Me and my neighbor were just preparing to do a little research about this. We got a grab a book from our local library but I think I learned more clear from this post. I am very glad to see such fantastic information being shared freely out there.

  2. Hi there, I found your website via Google while searching for a related topic, your site came up, it looks good. I have bookmarked it in my google bookmarks.

Leave a Reply

Your email address will not be published. Required fields are marked *