Home » SQL & PL/SQL » SQL & PL/SQL » Update same column with different values (Oracle 11.2)
| Update same column with different values [message #572092] |
Thu, 06 December 2012 01:15  |
lakshmis
Messages: 100
Registered: November 2008
Location: India
|
Senior Member |
|
|
Hi All,
Please help me out in updating the column values.
Test data:
CREATE TABLE test_at(nr_a NUMBER, nr_id NUMBER) TABLESPACE ECLP_DATA01
INSERT INTO test_att VALUES(90270091,NULL);
INSERT INTO test_att VALUES(90270091,NULL);
CREATE TABLE test_d(nr_a_t NUMBER, nr_id NUMBER) TABLESPACE ECLP_DATA01
INSERT INTO test_d VALUES(90270091, 90384721);
INSERT INTO test_d VALUES(90270091, 90384718);
SQL> SELECT * FROM test_d;
NR_A_T NR_ID
---------- ----------
90270091 90384721
90270091 90384718
SQL> SELECT * FROM test_at;
NR_A NR_ID
---------- ----------
90270091
90270091
Expected output:
SELECT * FROM test_at;
NR_A NR_ID
---------- ----------
90270091 90384721
90270091 90384718
I need to update "nr_id" column of test_at table with the values from test_d table. They can be udpated in any order.
I tried to update using rownum but it failed with "single row subquery returns more than 1 row...."
Any pointer to solve this will be helpful to me.
Regards,
Lakshmi.
|
|
|
|
| Re: Update same column with different values [message #572094 is a reply to message #572092] |
Thu, 06 December 2012 01:22  |
 |
Michel Cadot
Messages: 54712
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Do NOT put any schema, tablespace or storage parameters in your test case:
SQL> CREATE TABLE test_at(nr_a NUMBER, nr_id NUMBER) TABLESPACE ECLP_DATA01
2
SQL> /
CREATE TABLE test_at(nr_a NUMBER, nr_id NUMBER) TABLESPACE ECLP_DATA01
*
ERROR at line 1:
ORA-00959: tablespace 'ECLP_DATA01' does not exist
Post a WORKING test case:
SQL> CREATE TABLE test_at(nr_a NUMBER, nr_id NUMBER);
Table created.
SQL> INSERT INTO test_att VALUES(90270091,NULL);
INSERT INTO test_att VALUES(90270091,NULL)
*
ERROR at line 1:
ORA-00942: table or view does not exist
What should happen if you have not the same number of rows for some id in the 2 tables?
Regards
Michel
[Updated on: Thu, 06 December 2012 01:23] Report message to a moderator |
|
|
|
| Re: Update same column with different values [message #572095 is a reply to message #572094] |
Thu, 06 December 2012 01:26 |
lakshmis
Messages: 100
Registered: November 2008
Location: India
|
Senior Member |
|
|
Hi Michel,
Sorry for the typos.
Please find the below working test cases:
DROP TABLE test_at;
CREATE TABLE test_at(nr_a NUMBER, nr_id NUMBER);
INSERT INTO test_at VALUES(90270091,NULL);
INSERT INTO test_at VALUES(90270091,NULL);
DROP TABLE test_d;
CREATE TABLE test_d(nr_a_t NUMBER, nr_id NUMBER);
INSERT INTO test_d VALUES(90270091, 90384721);
INSERT INTO test_d VALUES(90270091, 90384718);
The number of rows for each nr_a will always be same in both the tables.
Regards,
Lakshmi.
|
|
|
|
|
|
|
|
| Re: Update same column with different values [message #572102 is a reply to message #572096] |
Thu, 06 December 2012 01:46 |
lakshmis
Messages: 100
Registered: November 2008
Location: India
|
Senior Member |
|
|
Hi Michel,
Suppose we have another row in test_d table,
INSERT INTO test_d VALUES(90270091, 90384719);
then the expected result is:
SELECT * FROM test_at;
NR_A NR_ID
---------- ----------
90270091 90384718
90270091 90384719
The min values must be populated and rest ignored.
@dariyoosh,
Thanks for the document.
Regards,
Lakshmi.
|
|
|
|
|
|
|
|
|
|
| Re: Update same column with different values [message #572114 is a reply to message #572111] |
Thu, 06 December 2012 03:38 |
 |
dariyoosh
Messages: 267
Registered: March 2009
Location: Iran / France
|
Senior Member |
|
|
lakshmis wrote on Thu, 06 December 2012 10:20
... But I will suggest for design changes(after reading the document you provided), but after providing the solution for this problem  ...
Rows in your oracle table don't have any specific order.
http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#i20438
Quote:
...
By default, a table is organized as a heap, which means that the database places rows where they fit best rather than in a user-specified order. Thus, a heap-organized table is an unordered collection of rows. As users add rows, the database places the rows in the first available free space in the data segment. Rows are not guaranteed to be retrieved in the order in which they were inserted.
Note: Index-organized tables use a different principle of organization. See "Overview of Index-Organized Tables".
...
So unless you define properly the primary key/foreign key for those tables, I don't see how can you match one row from one table with one row from another table (according to what criterion ??).
Regards,
Dariyoosh
[Updated on: Thu, 06 December 2012 03:39] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Update same column with different values [message #572128 is a reply to message #572124] |
Thu, 06 December 2012 05:36 |
lakshmis
Messages: 100
Registered: November 2008
Location: India
|
Senior Member |
|
|
Hi Michel,
Thanks for your suggestion and it worked.
Please find the below blocks:
DROP TABLE test_at;
CREATE TABLE test_at(nr_a NUMBER, nr_id NUMBER);
INSERT INTO test_at VALUES(90270091,NULL);
INSERT INTO test_at VALUES(90270091,NULL);
DROP TABLE test_d;
CREATE TABLE test_d(nr_a_t NUMBER, nr_id NUMBER);
INSERT INTO test_d VALUES(90270091, 90384721);
INSERT INTO test_d VALUES(90270091, 90384718);
SQL> SELECT * FROM test_at;
NR_A NR_ID
---------- ----------
90270091
90270091
SQL> DECLARE
2 CURSOR c_rec
3 IS
4 SELECT nr_a_t, nr_id FROM test_d;
5 c_nr NUMBER;
6 c_id NUMBER;
7 cntr NUMBER := 0;
8 TYPE num_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
9 TYPE row_id IS TABLE OF ROWID INDEX BY PLS_INTEGER;
10 ri row_id;
11 ta_nr_a num_list;
12 rn num_list;
13 BEGIN
14 SELECT ROWID, nr_a BULK COLLECT INTO ri, ta_nr_a FROM test_at;
15 OPEN c_rec;
16 LOOP
17 cntr := cntr + 1;
18 FETCH c_rec
19 INTO c_nr, c_id;
20 EXIT WHEN c_rec%NOTFOUND;
21 UPDATE test_at
22 SET nr_id = c_id
23 WHERE ROWID = ri (cntr);
24 END LOOP;
25 END;
26 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM test_at;
NR_A NR_ID
---------- ----------
90270091 90384721
90270091 90384718
SQL>
The above block has a limitation that, equal number of rows must be present in both the tables.
Thanks for your suggestion,
Regards,
Lakshmi.
|
|
|
|
| Re: Update same column with different values [message #572133 is a reply to message #572128] |
Thu, 06 December 2012 06:49 |
Solomon Yakobson
Messages: 1443
Registered: January 2010
|
Senior Member |
|
|
There is no need for PL/SQL. It can be done in SQL with MERGE statement:
SQL> select *
2 from test_att
3 /
NR_A NR_ID
---------- ----------
90270091
90270091
SQL> select *
2 from test_d
3 /
NR_A_T NR_ID
---------- ----------
90270091 90384721
90270091 90384718
SQL> merge
2 into test_att a
3 using (
4 with t1 as (
5 select nr_a,
6 rowid rid,
7 row_number() over(partition by nr_a order by 1) rn
8 from test_att
9 where nr_id is null
10 ),
11 t2 as (
12 select nr_a_t,
13 nr_id,
14 row_number() over(partition by nr_a_t order by nr_id) rn
15 from test_d
16 where nr_id is not null
17 )
18 select rid,
19 nr_id
20 from t1,
21 t2
22 where t2.nr_a_t = t1.nr_a
23 and t2.rn = t1.rn
24 ) b
25 on (
26 a.rowid = b.rid
27 )
28 when matched
29 then update
30 set a.nr_id = b.nr_id
31 /
2 rows merged.
SQL> select *
2 from test_att
3 /
NR_A NR_ID
---------- ----------
90270091 90384721
90270091 90384718
SQL>
SY.
|
|
|
|
| Re: Update same column with different values [message #572134 is a reply to message #572128] |
Thu, 06 December 2012 06:57 |
Solomon Yakobson
Messages: 1443
Registered: January 2010
|
Senior Member |
|
|
Code you posted will not work if tables have multiple NR_A's:
SQL> select *
2 from test_att
3 /
NR_A NR_ID
---------- ----------
1
90270091
90270091
SQL> select *
2 from test_d
3 /
NR_A_T NR_ID
---------- ----------
90270091 90384721
90270091 90384718
1 99
SQL> DECLARE
2 CURSOR c_rec
3 IS
4 SELECT nr_a_t, nr_id FROM test_d;
5 c_nr NUMBER;
6 c_id NUMBER;
7 cntr NUMBER := 0;
8 TYPE num_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
9 TYPE row_id IS TABLE OF ROWID INDEX BY PLS_INTEGER;
10 ri row_id;
11 ta_nr_a num_list;
12 rn num_list;
13 BEGIN
14 SELECT ROWID, nr_a BULK COLLECT INTO ri, ta_nr_a FROM test_att;
15 OPEN c_rec;
16 LOOP
17 cntr := cntr + 1;
18 FETCH c_rec
19 INTO c_nr, c_id;
20 EXIT WHEN c_rec%NOTFOUND;
21 UPDATE test_att
22 SET nr_id = c_id
23 WHERE ROWID = ri (cntr);
24 END LOOP;
25 END;
26 /
PL/SQL procedure successfully completed.
SQL> select *
2 from test_att
3 /
NR_A NR_ID
---------- ----------
1 90384721
90270091 90384718
90270091 99
SQL>
And:
SQL> select *
2 from test_att
3 /
NR_A NR_ID
---------- ----------
1
90270091
90270091
SQL> select *
2 from test_d
3 /
NR_A_T NR_ID
---------- ----------
90270091 90384721
90270091 90384718
1 99
SQL> merge
2 into test_att a
3 using (
4 with t1 as (
5 select nr_a,
6 rowid rid,
7 row_number() over(partition by nr_a order by 1) rn
8 from test_att
9 where nr_id is null
10 ),
11 t2 as (
12 select nr_a_t,
13 nr_id,
14 row_number() over(partition by nr_a_t order by nr_id) rn
15 from test_d
16 where nr_id is not null
17 )
18 select rid,
19 nr_id
20 from t1,
21 t2
22 where t2.nr_a_t = t1.nr_a
23 and t2.rn = t1.rn
24 ) b
25 on (
26 a.rowid = b.rid
27 )
28 when matched
29 then update
30 set a.nr_id = b.nr_id
31 /
3 rows merged.
SQL> select *
2 from test_att
3 /
NR_A NR_ID
---------- ----------
1 99
90270091 90384721
90270091 90384718
SQL>
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jun 19 15:11:03 CDT 2013
Total time taken to generate the page: 0.28332 seconds
|
] 
Members
Search
Help
Register
Login
Home
