Home » SQL & PL/SQL » SQL & PL/SQL » partial Updation of coumn ! (merged 4)
icon5.gif  partial Updation of coumn ! (merged 4) [message #420425] Sat, 29 August 2009 07:46 Go to next message
d_yadav
Messages: 4
Registered: March 2009
Location: INDIA
Junior Member
Dear All,

DECLARE
V_METER_NO VARCHAR2 := NULL;
V_FINAL_MF NUMBER(10, 2) := 1;

BEGIN
UPDATE METER_MASTER MM
SET MM.METER_NO = V_METER_NO, MM.FINAL_MF = V_FINAL_MF
WHERE MM.SUBDIVISION_CODE = P_SUBDIVN AND
MM.CUSTOMER_NO = G03.CONSUMER_NO;
END;
---------------------------

In above query V_METER_NO is null so I dont want to update MM.METER_NO column of METER_MASTER table .
But variable V_FINAL_MF is having value 1 so MM.FINAL_MF column sould be updated of METER_MASTER table only.
My problem is if variable V_METER_NO or V_FINAL_MF is not null then only that particular column should be updated.
I have to use only same above query.
Even I dont want to update the old value of that column.

Is it possible? Please guide me for above query?

Thanks,
Dhananjay.

[Updated on: Sat, 29 August 2009 09:15] by Moderator

Report message to a moderator

Re: partial Updation of coumn ! [message #420426 is a reply to message #420425] Sat, 29 August 2009 08:43 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
SET MM.METER_NO = nvl(V_METER_NO, MM.METER_NO)
Re: partial Updation of coumn ! (merged 4) [message #420427 is a reply to message #420425] Sat, 29 August 2009 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: partial Updation of coumn ! [message #420511 is a reply to message #420426] Mon, 31 August 2009 00:50 Go to previous messageGo to next message
d_yadav
Messages: 4
Registered: March 2009
Location: INDIA
Junior Member
Hi c_stenersen,
thanks for your reply

by using
SET MM.METER_NO = nvl(V_METER_NO, MM.METER_NO)

then actually updation of 'meter_master' table, one trigger is being called and there

I wanted :new.meter_no as null, because variable V_METER_NO
is having null value in procedure.

but as per above logic
I will get old meter no with :new.meter_no which I dont want.

Please guide me.


Awaiting for your valuable reply,
Dhananjay.
Re: partial Updation of coumn ! [message #420583 is a reply to message #420511] Mon, 31 August 2009 11:21 Go to previous message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Now I'm really not sure what you actually want...

Quote:
In above query V_METER_NO is null so I dont want to update MM.METER_NO column of METER_MASTER table .
But variable V_FINAL_MF is having value 1 so MM.FINAL_MF column sould be updated of METER_MASTER table only.


Quote:
I wanted :new.meter_no as null, because variable V_METER_NO
is having null value in procedure.

but as per above logic
I will get old meter no with :new.meter_no which I dont want.


First you say you don't want to update MM.meter_no, and now you say you want to have the null value passed in.. If you want :new.meter_no to be null in your trigger, then you'll have to give it the value null in the update.

But if you're using this in a before update trigger, just check if your :new.meter_no is null, and if so, set it to :old.meter_no. (What does your trigger actually do anyways, since you need it to receive null?)
Previous Topic: how to write it shorter or easier?
Next Topic: NUMBER EQUALS
Goto Forum:
  


Current Time: Fri Dec 09 06:12:58 CST 2016

Total time taken to generate the page: 0.14357 seconds