Cheap VPS & Xen Server

Residential Proxy Network - Hourly & Monthly Packages

Oracle UPDATE


In Oracle, UPDATE statement is used to update the existing records in a table. You can update a table in 2 ways.

Traditional Update table method

Syntax:

  1. UPDATE table
  2. SET column1 = expression1,
  3.     column2 = expression2,
  4.     …
  5.     column_n = expression_n
  6. WHERE conditions;

Update Table by selecting rocords from another table

Syntax:

  1. UPDATE table1
  2. SET column1 = (SELECT expression1
  3.                FROM table2
  4.                WHERE conditions)
  5. WHERE conditions;

Parameters:

1) column1, column2, … column_n:

It specifies the columns that you want to update.

2) expression1, expression2, …expression_n:

This specifies the values to assign to the column1, column2, ?. column_n.

3) conditions:It specifies the conditions that must be fulfilled for execution of UPDATE stateme.

Oracle Update Example: (Update single column)

  1. UPDATE suppliers
  2. SET supplier_name = ‘Kingfisher’
  3. WHERE supplier_id = 2;

This example will update the supplier_name as “Kingfisher” where “supplier_id” is 2.

Oracle Update Example: (Update multiple columns)

The following example specifies how to update multiple columns in a table. In this example, two columns supplier_name and supplier_address is updated by a single statement.

  1. UPDATE suppliers
  2. SET supplier_address = ‘Agra’,
  3.     supplier_name = ‘Bata shoes’
  4. WHERE supplier_id = 1;

Output:

1 row(s) updated.
0.06 seconds

Oracle Update Example: (By selecting records from another table)

  1. UPDATE customers
  2. SET name = (SELECT supplier_name
  3.                  FROM suppliers
  4.                  WHERE suppliers.supplier_name = customers.name)
  5. WHERE age < 25;

Output:

2 row(s) updated.
0.02 seconds

Here, the customers table is updated by fetching the data from “suppliers” table.

Comments

comments