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 Go to next message
lakshmis
Messages: 101
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58929
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 Go to previous messageGo to next message
lakshmis
Messages: 101
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 #572096 is a reply to message #572095] Thu, 06 December 2012 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58929
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But how Oracle can know the number will ALWAYS be the same in both tables?

Regards
Michel
Re: Update same column with different values [message #572097 is a reply to message #572095] Thu, 06 December 2012 01:34 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
@lakshmis
Please read Normalization


Regards,
Dariyoosh

[Updated on: Thu, 06 December 2012 01:34]

Report message to a moderator

Re: Update same column with different values [message #572102 is a reply to message #572096] Thu, 06 December 2012 01:46 Go to previous messageGo to next message
lakshmis
Messages: 101
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 #572104 is a reply to message #572102] Thu, 06 December 2012 01:52 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Which precisely shows that you didn't read Normalization.

Regards,
Dariyoosh

[Updated on: Thu, 06 December 2012 01:52]

Report message to a moderator

Re: Update same column with different values [message #572106 is a reply to message #572102] Thu, 06 December 2012 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 58929
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understand what you want to do but you don't understand what I said.
SQL works on set, your table is not a set, so (pure) SQL can't work.

Regards
Michel
Re: Update same column with different values [message #572111 is a reply to message #572106] Thu, 06 December 2012 03:20 Go to previous messageGo to next message
lakshmis
Messages: 101
Registered: November 2008
Location: India
Senior Member
Hi Michel,
Is there any way in which this can be done?( not only pure SQL)

@dariyoosh,
I understand that the design is flawed, but I will suggest for design changes(after reading the document you provided), but after providing the solution for this problem Sad

Regards,
Lakshmi.
Re: Update same column with different values [message #572114 is a reply to message #572111] Thu, 06 December 2012 03:38 Go to previous messageGo to next message
dariyoosh
Messages: 532
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 Sad ...


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 #572116 is a reply to message #572114] Thu, 06 December 2012 03:44 Go to previous messageGo to next message
lakshmis
Messages: 101
Registered: November 2008
Location: India
Senior Member
Hi,

Even I find it very weired, but have no choice Sad

Regards,
Lakshmi.
Re: Update same column with different values [message #572118 is a reply to message #572116] Thu, 06 December 2012 03:48 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
lakshmis wrote on Thu, 06 December 2012 10:44
... Even I find it very weired, but have no choice ... Sad

Seems, you don't follow me. No matter how weird it is, you cannot change the oracle concept. It is your problem that has to be adapted to oracle concept. It doesn't work other way.


Regards,
Dariyoosh
Re: Update same column with different values [message #572124 is a reply to message #572111] Thu, 06 December 2012 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 58929
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any way in which this can be done?( not only pure SQL)


Use a PL/SQL loop, for instance.

Regards
Michel
Re: Update same column with different values [message #572128 is a reply to message #572124] Thu, 06 December 2012 05:36 Go to previous messageGo to next message
lakshmis
Messages: 101
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2002
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2002
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.
Re: Update same column with different values [message #572185 is a reply to message #572134] Fri, 07 December 2012 01:12 Go to previous message
lakshmis
Messages: 101
Registered: November 2008
Location: India
Senior Member
Hi Solomon,

Thanks for figuring out the bug in my code and thanks a lot for your excellent solution.

Regards,
Lakshmi.
Previous Topic: Error message with no column name - does not match expected data length for column '[Xyz.Oracle].'''
Next Topic: Different plans on select query
Goto Forum:
  


Current Time: Thu Aug 28 10:54:19 CDT 2014

Total time taken to generate the page: 0.10392 seconds