Home » SQL & PL/SQL » SQL & PL/SQL » Updateing distinct values using another table (merged 6) (ora10g)
Updateing distinct values using another table (merged 6) [message #424714] Mon, 05 October 2009 09:49 Go to next message
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 [message #424722 is a reply to message #424714] Mon, 05 October 2009 10:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Asking the same question 3 times is more likely to get you 1/3 of the help than 3x the help.

Can you guarantee that there will always be the same number of rows in test_ank1 as there are in test_ank2?
Re: Updateing distinct values using another table (merged 3) [message #424723 is a reply to message #424714] Mon, 05 October 2009 10:26 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom wrote on Mon, 05 October 2009 17:29
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.

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

Re: Updateing distinct values using another table (merged 3) [message #424824 is a reply to message #424725] Tue, 06 October 2009 02:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd generally do it by creating a staging table, but you could add and then drop ta column from the table if you wanted.
Re: Updateing distinct values using another table (merged) [message #424838 is a reply to message #424714] Tue, 06 October 2009 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why are you reposting twice the same thing you already posted 3 times yesterday?
Your behaviour does not deserve any help.

Regards
Michel

[Updated on: Tue, 06 October 2009 03:36]

Report message to a moderator

Re: Updateing distinct values using another table [message #424839 is a reply to message #424714] Tue, 06 October 2009 03:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Given that you've not answered my questions in the previous thread on this topic, I'm not going to respond to this thread, and as this is a duplicate question, I'll lock it.

[too slow - it got merged instead]

[Updated on: Tue, 06 October 2009 03:36]

Report message to a moderator

Re: Updateing distinct values using another table (merged 6) [message #424845 is a reply to message #424714] Tue, 06 October 2009 03:55 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

STOP SPAMMING THE SITE WITH YOUR QUESTION.

Regards
Michel
Previous Topic: Table partitioning
Next Topic: double quotes in query (merged)
Goto Forum:
  


Current Time: Thu Sep 29 09:01:31 CDT 2016

Total time taken to generate the page: 0.17069 seconds