Updateing distinct values using another table (merged 6) [message #424714] |
Mon, 05 October 2009 09:49  |
sonu_A
Messages: 1 Registered: October 2009
|
Junior Member |
|
|
Hi,
I have 2 tables as below:
SQL> select * from test_ank1;
ORDNO STATUS
---------- -----
1 a
1 b
SQL> select * from test_ank2;
ORDNO STATUS
---------- -----
1
1
I want to update test_ank2 with values of test_ank1 and the final result should look like:
SQL> select * from test_ank2;
ORDNO STATUS
---------- -----
1 a
1 b
I am usin the following procedure:
CREATE OR REPLACE procedure proc_ank AS
cursor c1 is
SELECT * FROM test_ank1;
V_rec1 test_ank1%ROWTYPE;
V_rec2 test_ank2%ROWTYPE;
CURSOR c2 IS
SELECT * FROM test_ank2;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_rec1;
EXIT WHEN C1%NOTFOUND;
OPEN c2;
LOOP
FETCH c2 INTO v_rec2;
EXIT WHEN C2%NOTFOUND;
IF v_rec1.ordno=v_rec2.ordno THEN
UPDATE test_ank2 SET status=v_rec1.status WHERE ordno= v_rec1.ordno;
END IF;
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
END;
But the update resulting from this procedure is giving the following result:
SQL> select * from test_ank2;
ORDNO STATUS
---------- -----
1 b
1 b
Please help!
[Updated on: Tue, 06 October 2009 03:55] by Moderator Report message to a moderator
|
|
|
|
Re: Updateing distinct values using another table (merged 3) [message #424723 is a reply to message #424714] |
Mon, 05 October 2009 10:26   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
1) It helps to check if your message has been posted before re-posting it.
2) When posting code we'd appreciate it if you used code tags - see the orafaq forum guide if you're not sure how.
3) The fundamental problem here is that the records in test_ank2 are identical. As such there is no way of saying which record in test_ank1 goes with which record in test_ank2.
There is no accurate way of solving this problem with an update. You'd need a unique identifier to be able to do so and you don't have one.
The simple solution would be to delete the records in test_ank2 then insert the records from test_ank1 into test_ank2.
[Updated on: Mon, 05 October 2009 10:26] Report message to a moderator
|
|
|
Re: Updateing distinct values using another table (merged 3) [message #424724 is a reply to message #424723] |
Mon, 05 October 2009 10:29   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If there are an identical number of records then it is possible - give each row a number using rownum or row_number() and then match the rows up like that.
It's totally random which row gets which value, but as the rows are all identical, that doesn't matter.
Of course, it's just conceivable that the OP is a gross oversimplification of the actual problem, and that more details will emerge as we progress.....
|
|
|
Re: Updateing distinct values using another table (merged 3) [message #424725 is a reply to message #424724] |
Mon, 05 October 2009 10:36   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
JRowbottom wrote on Mon, 05 October 2009 17:29If there are an identical number of records then it is possible - give each row a number using rownum or row_number() and then match the rows up like that.
This requires to add a column to the table if you want to do it in SQL otherwise only a procedural way is possible.
Regards
Michel
[Updated on: Mon, 05 October 2009 10:38] Report message to a moderator
|
|
|
|
|
|
|