Home » SQL & PL/SQL » SQL & PL/SQL » Updation Problem
Updation Problem [message #219341] Wed, 14 February 2007 01:31 Go to next message
gsanthosh12
Messages: 4
Registered: February 2007
Junior Member
Hi Guys,

I m facing some problem with Procedure Updation.
The records are not updating properly.
The code as follows:
----------------------------

CREATE OR REPLACE PROCEDURE INNEW AS
CURSOR c_mis IS SELECT slno FROM FEDATA WHERE blank IS NULL ;
v_distcode ALLDATA.dist_code%TYPE;
v_distname ALLDATA.dist_name%TYPE;
BEGIN
FOR i IN c_mis LOOP
BEGIN
SELECT dist_code,dist_name
INTO v_distcode,v_distname
FROM ALLDATA WHERE serial_no LIKE i.slno AND ROWNUM <2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
UPDATE FEDATA SET blank = v_distcode,blank1=v_distname
WHERE slno=i.slno;

COMMIT;
END LOOP;

END;
/

--------------------------------

i m trying to update the FEDATA table.
can u help me for changing the above code??

Thanks in advance
Santhsoh
Re: Updation Problem [message #219347 is a reply to message #219341] Wed, 14 February 2007 02:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
The records are not updating properly.
What do you mean by this?
Quote:
UPDATE FEDATA SET blank = v_distcode,blank1=v_distname
WHERE slno=i.slno;
Also what exactly you want to update when the data is not there in ALLDATA table?
Now it will insert the previous record's data, as you have not assigned NULL's to the variables.

By
Vamsi
Re: Updation Problem [message #219364 is a reply to message #219341] Wed, 14 February 2007 03:04 Go to previous messageGo to next message
gsanthosh12
Messages: 4
Registered: February 2007
Junior Member
Hi Vamsi,

if the data doesnt exist in the alldata table the blank & blank1 is set to null.
Re: Updation Problem [message #219367 is a reply to message #219364] Wed, 14 February 2007 03:16 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
Also what exactly you want to update when the data is not there in ALLDATA table?
Now it will insert the previous record's data, as you have not assigned NULL's to the variables.

Quote:
if the data doesnt exist in the alldata table the blank & blank1 is set to null.
Is this the answer for my first question? (Or) Aren't you agreeing with my second statement?

I'm not clear about your concern. Can you PLEASE ELABORATE your problem?
By
Vamsi
Re: Updation Problem [message #219371 is a reply to message #219341] Wed, 14 February 2007 03:25 Go to previous messageGo to next message
gsanthosh12
Messages: 4
Registered: February 2007
Junior Member
Hi Vamsi,

I m agreeing o u r answer.my concern is the the procedure is not updating the records properly with my code i sent .
i have done the modification by replaing = with like in the procedure(which u suggested to do).

if the data doesnt exists in the alldata table the blank & blank1 values will be set to null.
i hope u r clear now..



Re: Updation Problem [message #219382 is a reply to message #219371] Wed, 14 February 2007 03:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, yes. The values will be set to null because you have issued an Update statement setting them to Null. In what way is this wrong?

Now, if what you're trying to say (and failing - this is just an educated guess) is that you don't want the update to happen if the rows don't exist in the table, then you could rewrite your code like this:
FOR i IN c_mis LOOP
  BEGIN
    SELECT dist_code,dist_name 
    INTO   v_distcode,v_distname
    FROM   ALLDATA 
    WHERE  serial_no LIKE i.slno 
    AND    ROWNUM <2;

    UPDATE FEDATA 
    SET    blank = v_distcode
          ,blank1= v_distname
    WHERE  slno=i.slno;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;
END LOOP;
COMMIT;

There are still some problems with your code, such as the 'ROWNUM < 2' line in the SELECT. Are you aware that this query will effectively pick one of the set of rows that match the where clause at random, and return that one. It isn't fetching some mystical 'first row' back.
Re: Updation Problem [message #219383 is a reply to message #219371] Wed, 14 February 2007 03:45 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
BEGIN
   v_distcode := NULL;
   v_distname := NULL;
   SELECT dist_code,dist_name
   INTO v_distcode,v_distname
   FROM ALLDATA 
   WHERE serial_no LIKE i.slno 
   AND ROWNUM <2;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      NULL;
END;
Have you assigned NULL's to the variables in each iteration.
Why don't you use a simple update instead of this cursor?

Edit: I really got confused.
Santhosh,
Do you want to update those columns with NULL, if there are no relevant data in ALLDATA?

By
Vamsi

[Updated on: Wed, 14 February 2007 03:59]

Report message to a moderator

Previous Topic: Primary keys and OIDs
Next Topic: Granting privileges on trigger
Goto Forum:
  


Current Time: Wed Dec 07 14:55:44 CST 2016

Total time taken to generate the page: 0.08923 seconds