Oracle – How to Update Nested Table Item

oracleupdate

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.

Best Answer

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;

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM 
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.

Oracle UPDATE followed by SELECT in LinqPad

Thank you all for your comments. You deserve the credit for this answer. To summarize:

The Oracle driver for LinqPad that I'm using (dotConnect Direct Mode based on OCI 8) wants the statement to be formatted like this (yes, wihout any semicolons):

UPDATE schedule_amounts 
SET ANNUAL_RATE = 44000
WHERE SCHEDULE_ID = 'LCSD'
    and SCHEDULE_NO = 2014
    and SCHEDULE_LEVEL = 100
    and SCHEDULE_STEP = 17
GO
SELECT * FROM schedule_amounts