oracle update lower,Oracle Update Statements

Basic Update Statements

Update all records

UPDATE SET =

CREATE TABLE test AS

SELECT object_name, object_type

FROM all_objs;

SELECT DISTINCT object_name

FROM test;

UPDATE test

SET object_name = 'OOPS';

SELECT DISTINCT object_name

FROM test;

ROLLBACK;

Update a specific record

UPDATE SET = WHERE =

SELECT DISTINCT object_name

FROM test;

UPDATE test

SET object_name = 'LOAD'

WHERE object_name = 'DUAL';

COMMIT;

SELECT DISTINCT object_name

FROM test

Update based on a single queried value

UPDATE SET = (

SELECT FROM WHERE )

WHERE ;

CREATE TABLE test AS

SELECT table_name, CAST('' AS VARCHAR2(30)) AS

lower_name

FROM user_tables;

desc test

SELECT *

FROM test

WHERE table_name LIKE '%A%';

SELECT *

FROM test

WHERE table_name NOT LIKE '%A%';

-- this is not a good thing ...

UPDATE test t

SET lower_name = (

SELECT DISTINCT LOWER(table_name)

FROM user_tables u

WHERE u.table_name = t.table_name

AND u.table_name LIKE '%A%');

-- look at the number of rows updated

SELECT * FROM test;

-- neither is this

UPDATE test t

SET lower_name = (

SELECT DISTINCT LOWER(table_name)

FROM user_tables u

WHERE u.table_name = t.table_name

AND u.table_name NOT LIKE '%A%');SELECT * FROM test;UPDATE test t

SET lower_name = (

SELECT DISTINCT LOWER(table_name)

FROM user_tables u

WHERE u.table_name = t.table_name

AND u.table_name LIKE '%A%')WHERE t.table_name LIKE '%A%';

SELECT * FROM test;

Update based on a query returning multiple values

UPDATE SET (,) = (

SELECT (,

)

FROM WHERE = )

WHERE ;

CREATE TABLE test AS

SELECT t. table_name, t. tablespace_name,  s.extent_management

FROM user_tables t, user_tablespaces s

WHERE t.tablespace_name = s. tablespace_name

AND 1=2;

desc test

SELECT * FROM test;

-- does not work

UPDATE test

SET (table_name, tablespace_name) = (

SELECT table_name, tablespace_name

FROM user_tables);

-- works

INSERT INTO test

(table_name, tablespace_name)

SELECT table_name, tablespace_name

FROM user_tables;

COMMIT;

SELECT *

FROM test

WHERE table_name LIKE '%A%';

-- does not work

UPDATE test t

SET tablespace_name, extent_management = (

SELECT tablespace_name, extent_management

FROM user_tables a, user_tablespaces u

WHERE t.table_name = a.table_name

AND a.tablespace_name = u.tablespace_name

AND t.table_name LIKE '%A%');

-- works but look at the number of rows updated

UPDATE test t

SET (tablespace_name, extent_management) = (

SELECT DISTINCT u.tablespace_name, u.extent_management

FROM user_tables a, user_tablespaces u

WHERE t.table_name = a.table_name

AND a.tablespace_name = u.tablespace_name

AND t.table_name LIKE '%A%');

ROLLBACK;

-- works properly

UPDATE test t

SET (tablespace_name,

extent_management) =

(

SELECT DISTINCT (u.tablespace_name, u.extent_management)

FROM user_tables a, user_tablespaces u

WHERE t.table_name = a.table_name

AND a.tablespace_name = u.tablespace_name)

WHERE t.table_name LIKE '%A%';

SELECT *

FROM test;

Update the results of a SELECT statement

UPDATE ()

SET = WHERE ;

SELECT *

FROM test

WHERE table_name LIKE '%A%';SELECT *

FROM test

WHERE table_name NOT LIKE '%A%';UPDATE (

SELECT *

FROM test

WHERE table_name NOT LIKE '%A%')SET extent_management = 'Unknown'

WHERE table_name NOT LIKE '%A%';

SELECT *

FROM test;

Correlated Update

Single column

UPDATE TABLE() SET = (

SELECT FROM WHERE = );

conn hr/hr

CREATE TABLE empnew AS

SELECT * FROM employees;

UPDATE empnew

SET salary = salary * 1.1;

UPDATE employees t1

SET salary = (

SELECT salary

FROM empnew t2

WHERE t1.employee_id = t2.employee_id);

drop table empnew;

Multi-column

UPDATE SET () = (

SELECT FROM WHERE );

CREATE TABLE t1 AS

SELECT table_name, tablespace_name

FROM user_tables

WHERE rownum < 11;

CREATE TABLE t2 AS

SELECT table_name,

TRANSLATE(tablespace_name,'AEIOU','VWXYZ') AS TABLESPACE_NAME

FROM user_tables

WHERE rownum < 11;

SELECT * FROM t1;

SELECT * FROM t2;

UPDATE t1 t1_alias

SET (table_name,

tablespace_name) = (

SELECT table_name, tablespace_name

FROM t2 t2_alias

WHERE t1_alias.table_name = t2_alias.table_name);

SELECT * FROM t1;

Nested Table Update

See Nested Tables page

Returning Clause demo

UPDATE ()

SET ....

WHERE ....

RETURNING INTO ;

conn hr/hr

var bnd1 NUMBER

var bnd2 VARCHAR2(30)

var bnd3 NUMBER

UPDATE employees

SET job_id ='SA_MAN', salary = salary + 1000,

department_id = 140

WHERE last_name = 'Jones'

RETURNING salary*0.25, last_name, department_idINTO :bnd1, :bnd2, :bnd3;

print bnd1

print bnd2

print bnd3

rollback;

conn hr/hr

variable bnd1 NUMBER

UPDATE employees

SET salary = salary * 1.1

WHERE department_id = 100

RETURNING SUM(salary) INTO :bnd1;

print bnd1

rollback;

Update Object Table

Update a table object

UPDATE SET VALUE () = (

)

WHERE ;

CREATE TYPE people_typ AS OBJECT (

last_name     VARCHAR2(25),

department_id NUMBER(4),

salary        NUMBER(8,2));

/

CREATE TABLE people_demo1 OF people_typ;

desc people_demo1

CREATE TABLE people_demo2 OF people_typ;

desc people_demo2

INSERT INTO people_demo1

VALUES (people_typ('Morgan', 10, 100000));

INSERT INTO people_demo2

VALUES (people_typ('Morgan', 10, 150000));

UPDATE people_demo1 p

SET VALUE(p) = (

SELECT VALUE(q) FROM people_demo2 q

WHERE p.department_id = q.department_id)

WHERE p.department_id = 10;

SELECT * FROM people_demo1;

Record Update

Update based on a record

Note: This construct updates every

column so use with care. May cause increased redo, undo, and foreign key

locking issues.

UPDATE SET ROW = WHERE ;

CREATE TABLE t AS

SELECT table_name, tablespace_name

FROM all_tables;

SELECT DISTINCT tablespace_name

FROM t;

DECLARE

trec  t%ROWTYPE;

BEGIN

trec.table_name := 'DUAL';

trec.tablespace_name := 'NEW_TBSP';

UPDATE t

SET ROW = trec

WHERE table_name = 'DUAL';

COMMIT;

END;

/

SELECT DISTINCT tablespace_name

FROM t;

Update Partitioned Table

Update only records in a single partition

UPDATE PARTITION ()

SET = WHERE ;

conn sh/sh

UPDATE sales PARTITION

(sales_q1_2005) s

SET s.promo_id = 494

WHERE amount_sold > 9000;