update multiple records in multiple nested tables in oracle
Perhaps the best reason for avoiding nested tables in a database is that they are hard to work with, and the syntax is underdocumented and hard to grok.
Moving on!
Here is a table with a nested table.
SQL> select f.force_name, t.id, t.name
2 from transformer_forces f, table(f.force_members) t
3 /
FORCE_NAME ID NAME
---------- ---------- --------------------
Autobot 0 Metroplex
Autobot 0 Optimus Prime
Autobot 0 Rodimus
Decepticon 0 Galvatron
Decepticon 0 Megatron
Decepticon 0 Starscream
Dinobot 0 Grimlock
Dinobot 0 Swoop
Dinobot 0 Snarl
9 rows selected.
SQL>
As you can see, each element in the nested table the ID attribute is set to zero in all cases. What we would like to do is update all of them. But, alas!
SQL> update table
2 ( select force_members from transformer_forces ) t
3 set t.id = rownum
4 /
( select force_members from transformer_forces ) t
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
SQL>
It is possible to update all the elements on a nested table for a single row in the holding table:
SQL> update table
2 ( select force_members from transformer_forces
3 where force_name = 'Autobot') t
4 set t.id = rownum
5 /
3 rows updated.
SQL>
But the only way of doing that for the whole table is a PL/SQL loop. Yuck!
There is an alternative: use a Nested Table Locator, via the NESTED_TABLE_GET_REFS hint. This is a particularly obscure thing (it's not in the main list of hints) but it does the trick:
SQL> update /*+ NESTED_TABLE_GET_REFS */ force_members_nt
2 set id = rownum
3 /
9 rows updated.
SQL> select f.force_name, t.id, t.name
2 from transformer_forces f, table(f.force_members) t
3 /
FORCE_NAME ID NAME
---------- ---------- --------------------
Autobot 1 Metroplex
Autobot 2 Optimus Prime
Autobot 3 Rodimus
Decepticon 4 Galvatron
Decepticon 5 Megatron
Decepticon 6 Starscream
Dinobot 7 Grimlock
Dinobot 8 Swoop
Dinobot 9 Snarl
9 rows selected.
SQL>
This hint allows us to bypass the holding table altogether and work with the actual nested table. That is, the object specified in the Nested Table storage clause:
create table transformer_forces (
force_name varchar2(10)
, force_members transformers_nt)
nested table force_members store as force_members_nt return as value;
^^^^^^^^^^^^^^^^
Update multiple records using subquery oracle
You need to add extra condition in your where clause:
Update Test_A
SET test_a.description
= (SELECT test_b.description From Test_B Where Test_A.id = Test_B.id And Test_B.Status='U')
Where EXISTS (SELECT id From Test_B WHERE test_a.id = test_b.id And Test_B.Status='U');
If there are no NULL descriptions in TEST_B you may try this:
Update Test_A
SET test_a.description
= NVL((SELECT test_b.description From Test_B Where Test_A.id = Test_B.id And Test_B.Status='U'), test_a.description);
In this case all rows will be updated, but if the correlated subquery returns NULL the old value will remain
What is the most efficient way of updating multiple records in Oracle with same value
One option to remove the 1000 elements in IN list restriction is to create another table, e.g.
create table order_ids (orderId number constraint pk_oid primary key);
and insert all ID values you're working with. It means not 1000 by 1000, but as many as needed (millions, possibly) at once.
Then
update order a set a.status = 'PROCESSED'
where exists (select null
from order_ids b
where b.orderId = a.orderId);
(table's primary key means that Oracle will implicitly index that column; if you allow duplicates, then remove primary key and create index on that column).
Related Topics
How to Add "Weights" to a MySQL Table and Select Random Values According to These
Sql Query for Time In/Out Attendance
Firstname, Lastname in Sql, Too Complex
How to Do a Count(Distinct) Using Window Functions with a Frame in SQL Server
Merging Two Sqlite Databases Which Both Have Junction Tables
Sql 2005 How to Use Keyword Like in a Case Statement
Date Split-Up Based on Fiscal Year
Using Where Clause with Between and Null Date Parameters
Update Multiple Rows Using Select Statement
Querying Multiple Tables in Big Query
T-Sql Row Number Restart After N
Grouping by Date, Return Row Even If No Records Found
Predict The Number of Rows in Output
How to Create an SQL Query That Groups by Value Ranges
Cast Collation of Nvarchar Variables in T-Sql
How to Drop a Column with Object Dependencies in SQL Server 2008
T/F: Using If Statements in a Procedure Produces Multiple Plans