sql problem [message #419742] |
Tue, 25 August 2009 07:37  |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
I want to update the emp table for the following case for a particular emp id
if null is passed is for salary then
check if the field for salary is empty or null
if empty or null then skip this field for update
If null is passed and if value exists for salary then skip the update for this field
if new value is sent for salary then check if it is same value in the field
if same skip the update
if value sent in is different then the one existing then update with the new value.
create or replace procedure upd_sal (in_sal number)
as
begin
Update emp set salary=DECODE(salary,null,salary,in_salary);
end;
/
please let me know if I can make in any other way.
|
|
|
|
Re: sql problem [message #419748 is a reply to message #419743] |
Tue, 25 August 2009 07:54   |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
I didn't understand you Sir.
"skip this field for update" -if it is then what I understood is
update the column ...
How can we use it where clause?
|
|
|
|
Re: sql problem [message #419753 is a reply to message #419748] |
Tue, 25 August 2009 08:11   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
It would appear that your simple description does not quite cover the requirements. You would be better to supply a working test case in order that we can work out what it is that you are actually trying to achieve.
|
|
|
Re: sql problem [message #419755 is a reply to message #419752] |
Tue, 25 August 2009 08:16   |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
Sir,
"if null is passed is for salary then
check if the field for salary is empty or null
if empty or null then skip this field for update
If null is passed and if value exists for salary then skip the update for this field
if new value is sent for salary then check if it is same value in the field
if same skip the update
if value sent in is different then the one existing then update with the new value.
"
if the above is the requirement what will be the solution?
I maen the sql code? How would u skip
Quote: |
"Skip" means WHERE clause, otherwise you don't skip you update. Then you don't need any DECODE in SET clause, you set with the new value each time you set.
Use a CASE expression (in WHERE clause) it is easier to write complex condition.
|
|
|
|
|
Re: sql problem [message #419760 is a reply to message #419758] |
Tue, 25 August 2009 08:24   |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
Sir, I did not mean that you are wrong.
This is some kinda requirement came on my way. Not sure what
will be the solution.
Quote: |
"Skip" means WHERE clause, otherwise you don't skip you update. Then you don't need any DECODE in SET clause, you set with the new value each time you set.
Use a CASE expression (in WHERE clause) it is easier to write complex condition.
|
I just want to know how u did this using sql ?
Thanks
|
|
|
Re: sql problem [message #419764 is a reply to message #419760] |
Tue, 25 August 2009 09:01   |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
if new value is sent for salary then check if it is same value in the field
if same skip the update
if value sent in is different then the one existing then update with the new value.
--Is there a way to check this?
[Updated on: Tue, 25 August 2009 09:12] Report message to a moderator
|
|
|
|
|
Re: sql problem [message #419769 is a reply to message #419765] |
Tue, 25 August 2009 09:31   |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
but
Update emp set salary=DECODE(salary,null,salary,in_salary);
satifies all the conditions!!!
Does below statement makes sense when doing update?
>if new value is sent for salary then check if it is same value in the field
>if same skip the update
>if value sent in is different then the one existing then update with the new value.
|
|
|
Re: sql problem [message #419778 is a reply to message #419769] |
Tue, 25 August 2009 09:59   |
c_stenersen
Messages: 255 Registered: August 2007
|
Senior Member |
|
|
Your statement doesn't really satisfy the conditions. (Now you're checking if salary is null, and if it is then you update it to null, the current value of salary.) What you need to test is the in parameter, not the current value of the salary field.set salary = nvl(in_sal, salary)
But the condition you mention in your first post, and the one you mention now are different. If in_sal is null, should the salary field be updated or not? (if no, like you say in your first post, then use a nvl, if yes, like you say in your last post, simply update to the new value).
This won't "skip" the update, but the result values would be the same, as BlackSwan says.
|
|
|
Re: sql problem [message #419780 is a reply to message #419742] |
Tue, 25 August 2009 10:25   |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
Can I know how to modifiy the requirement
"if null is passed is for salary then
check if the field for salary is empty or null
if empty or null then skip this field for update
If null is passed and if value exists for salary then skip the update for this field
if new value is sent for salary then check if it is same value in the field
if same skip the update
if value sent in is different then the one existing then update with the new value." for salary update?
|
|
|
|
|
|
|
Re: sql problem [message #419787 is a reply to message #419785] |
Tue, 25 August 2009 10:43   |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
Sir, its being said that salary column is updateable column
but on the other hand its mentioned that it needs to be skipped if it matches the passing value
|
|
|
|
|
Re: sql problem [message #419791 is a reply to message #419742] |
Tue, 25 August 2009 10:58   |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
Quote: |
The unequal comparison operator is also something that's useful for this, as you are mentioning yourself.
|
cant be a good idea!
|
|
|
Re: sql problem [message #419792 is a reply to message #419791] |
Tue, 25 August 2009 11:05   |
c_stenersen
Messages: 255 Registered: August 2007
|
Senior Member |
|
|
In combination with not null, you would be able to find out if they are the same or not. (Just checking for equality wouldn't work, as a null value is never equal or not equal to anything).
But now you're all of a sudden talking about updating several columns, and not just one, which makes it a different matter. I would just use a nvl, and update all rows..
|
|
|
Re: sql problem [message #419794 is a reply to message #419792] |
Tue, 25 August 2009 11:14   |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
set salary = nvl(in_salary,nvl2(salary,in_salary,salary))
will probably work
it will satisfy the conditions except the euqlity check condition
[If new value is sent for salary then check if it is same value in the field
if same skip the update
if value sent in is different then the one existing then update with the new value.]
I need to do the same for multiple columns for the same table
|
|
|
|
|
|
|
|
Re: sql problem [message #419808 is a reply to message #419796] |
Tue, 25 August 2009 12:06   |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
update emp set salary=in_salary
where (salary != in_salary and in_salary is not null) or (salary is null and in_salary is null)
and emp_id=in_emp_id
this way?
|
|
|
|
|
|
|
Re: sql problem [message #419813 is a reply to message #419794] |
Tue, 25 August 2009 12:24   |
c_stenersen
Messages: 255 Registered: August 2007
|
Senior Member |
|
|
For multiple columns, use nvl..
But this is wrong:Quote: | set salary = nvl(in_salary,nvl2(salary,in_salary,salary))
|
You're checking if in_salary is null. If no then you're using in_salary, which is correct. But if it is, then you're using in_salary (which is null) if salary is not null. If salary's null you're using salary (so null again).
If in_salary is not null, it will work correctly. Otherwise you will always update the column to null, rather than keeping the old value of salary, as you intended.http://www.techonthenet.com/oracle/functions/nvl2.php
update emp
set salary = nvl(in_salary, salary) --Add the rest of the columns you wanted to update
where emp_id = in_emp_id
Updates the column to the value of in_salary if the value isn't null, and keeps the old value otherwise. You don't need to check if the old value was null. You'd anyways just be updating it to null (its old value).
[Updated on: Tue, 25 August 2009 12:26] Report message to a moderator
|
|
|
|
Re: sql problem [message #419815 is a reply to message #419814] |
Tue, 25 August 2009 12:36   |
c_stenersen
Messages: 255 Registered: August 2007
|
Senior Member |
|
|
This I believe was bad wording from the OP... He's now saying he wants to update several columns. I interpret this to be: change the value of the column if the new value is not null, and keep the old value if it is.
|
|
|
Re: sql problem [message #419816 is a reply to message #419780] |
Tue, 25 August 2009 12:39   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
nishita2009 wrote on Tue, 25 August 2009 11:25 |
If null is passed and if value exists for salary then skip the update for this field
|
You're getting nowhere fast. there are many problems (not just one or two) with your thinking and code. I'll point out just one as others have pointed out other problems.
The logic you use is totally flawed. What you say is that if you pass in a NULL but there is something in the salary column already, you update it to itself. End product is yes, the update will be "skipped" but you are still updating the column and the overhead of all the undo and logging of this still exists as if you updated it to another value. Say you have a billion rows and you update the column to itself. In the end, no change was done, but the overhead is enormous.
Oh, by the way, your IN parameter is not used in your procedure, and you use a parameter that does not exist (since we're pointing out all of the problems).
|
|
|
Re: sql problem [message #419817 is a reply to message #419816] |
Tue, 25 August 2009 12:42   |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
"if null is passed is for salary then
check if the field for salary is empty or null
if empty or null then skip this field for update
If null is passed and if value exists for salary then skip the update for this field
if new value is sent for salary then check if it is same value in the field
if same skip the update
if value sent in is different then the one existing then update with the new value."
I am totally lost trying to find the logic!
|
|
|
|