1

I'm really new on oracle db. I'm trying to update specific column on this table.

CREATE TYPE ABSTRACT_SALARY AS OBJECT (
startDate DATE,
endDate DATE,
salary NUMBER );

CREATE TYPE SALARY_TYPE AS TABLE OF ABSTRACT_SALARY;

CREATE TABLE Person(
---
---
---
---
salaryType SALARY_TYPE
)
NESTED TABLE salaryType STORE AS person_salaryTypes;

How can I update salary column %10 higher. Thanks in advance.

3

Update all rows in the whole nested table:

update /*+ NESTED_TABLE_GET_REFS */ person_salaryTypes
set salary = salary * 1.1;
commit;

Update all rows in the nested table for 1 person:

update table(select p.salarytype from person p where p.id = 1)
set salary = salary * 1.1;
commit;

Update 1 row in the nested table for 1 person:

update table(select p.salarytype from person p where p.id = 1)
set salary = salary * 1.1
where startdate = ...;
commit;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.