Home » SQL & PL/SQL » SQL & PL/SQL » sql problem (Oracle 9.2.0.3)
sql problem [message #419742] Tue, 25 August 2009 07:37 Go to next message
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 #419743 is a reply to message #419742] Tue, 25 August 2009 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"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.

Regards
Michel
Re: sql problem [message #419748 is a reply to message #419743] Tue, 25 August 2009 07:54 Go to previous messageGo to next message
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 #419752 is a reply to message #419748] Tue, 25 August 2009 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
"skip this field for update" -if it is then what I understood is
update the column ...

I understand the opposite, so precise your requirements.

Regards
Michel
Re: sql problem [message #419753 is a reply to message #419748] Tue, 25 August 2009 08:11 Go to previous messageGo to next message
pablolee
Messages: 2835
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 Go to previous messageGo to next message
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 #419758 is a reply to message #419755] Tue, 25 August 2009 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I provided the answer from what I understood.
You tell I'm wrong in my understanding.
What do you expect we answer if you don't explain the requirements or post a test case to clarify them as pablolee asked you.

Regards
Michel
Re: sql problem [message #419760 is a reply to message #419758] Tue, 25 August 2009 08:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #419765 is a reply to message #419764] Tue, 25 August 2009 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes in the same way you say it in a WHERE clause.
See UPDATE

Regards
Michel

[Updated on: Tue, 25 August 2009 12:16]

Report message to a moderator

Re: sql problem [message #419766 is a reply to message #419742] Tue, 25 August 2009 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior 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?

Simply always do update, will provide same/desired data in table
Re: sql problem [message #419769 is a reply to message #419765] Tue, 25 August 2009 09:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #419782 is a reply to message #419769] Tue, 25 August 2009 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
nishita2009 wrote on Tue, 25 August 2009 16:31
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.

Not at all in many ways.
if same skip the update
1/ You don't check if it is the same value
2/ you don't skip the update you update with the same value. Updating is not skipping.

Once again, skipping is WHERE clause. Try to understand this. At least until your statement will have a WHERE clause it is wrong.

Regards
Michel


Re: sql problem [message #419783 is a reply to message #419742] Tue, 25 August 2009 10:30 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
salary = nvl(in_salary,nvl2(salary,in_salary,salary))


Is the above satisfies previous requirement? Or is it not a good approach to write this way?

[Updated on: Tue, 25 August 2009 10:33]

Report message to a moderator

Re: sql problem [message #419784 is a reply to message #419783] Tue, 25 August 2009 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is the above satisfies previous requirement?

No.
Quote:
Or is it not a good approach to write this way?

It is not good, see all my posts.

Regards
Michel
Re: sql problem [message #419785 is a reply to message #419784] Tue, 25 August 2009 10:36 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
Sir, would you please tell me how am I wrong? How to skip using where clause? The UPDATE link you provided is not working.


Should I need to put salary!=in_salary ? something like that

[Updated on: Tue, 25 August 2009 10:37]

Report message to a moderator

Re: sql problem [message #419787 is a reply to message #419785] Tue, 25 August 2009 10:43 Go to previous messageGo to next message
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 #419788 is a reply to message #419780] Tue, 25 August 2009 10:50 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Try to do it yourself. What do you think needs to be in the where condition?? You know that a null value in the field given in means that the value shouldn't be updated, and a not null means the value should be updated if the new value is different from the old one. To make it simple, "is null", "is not null", "and" and "or" are things you could think about using to solve this. The unequal comparison operator is also something that's useful for this, as you are mentioning yourself.

If you want to use the where clause in an update you can write it like this:
update emp 
set salary = salary_in 
where ...
Replace ... with the conditions for the update. If the expression evaluates to true the row will be updated. Otherwise not (it will be skipped).... Smile
Re: sql problem [message #419789 is a reply to message #419788] Tue, 25 August 2009 10:55 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
if there are multiple columns that needs to be updated in the same way?
Re: sql problem [message #419791 is a reply to message #419742] Tue, 25 August 2009 10:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #419796 is a reply to message #419794] Tue, 25 August 2009 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
nishita2009 wrote on Tue, 25 August 2009 18:14
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

You can repeat it as many times as you want to convince you but IT DOES NOT SATISFY the requirements.

And for UPDATE statement, read from top to bottom, the link I posted.

Regards
Michel

Re: sql problem [message #419801 is a reply to message #419796] Tue, 25 August 2009 11:51 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
The LINK you provided is not working.It returns to same page
Re: sql problem [message #419804 is a reply to message #419742] Tue, 25 August 2009 11:55 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
How would it satisfy would you please tell????
Its not that I did not tried
Re: sql problem [message #419805 is a reply to message #419742] Tue, 25 August 2009 11:58 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
Can I DO IT FOR MULTI COLUMN UPDATE with where clause? Its not possible and carries no sense to check matching value while updating here

[Updated on: Tue, 25 August 2009 11:59]

Report message to a moderator

Re: sql problem [message #419806 is a reply to message #419742] Tue, 25 August 2009 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Can I DO IT FOR MULTI COLUMN UPDATE with where clause?
If you can't make it work for 1 column, you stand no chance to make it work for multiple columns.

First make it work, then make it fancy!
Re: sql problem [message #419808 is a reply to message #419796] Tue, 25 August 2009 12:06 Go to previous messageGo to next message
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 #419809 is a reply to message #419742] Tue, 25 August 2009 12:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>this way?
>and emp_id=in_emp_id
Scope CREEP?

>create or replace procedure upd_sal (in_sal number)
How, when, where did in_emp_id enter this problem
Re: sql problem [message #419810 is a reply to message #419801] Tue, 25 August 2009 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
nishita2009 wrote on Tue, 25 August 2009 18:51
The LINK you provided is not working.It returns to same page

And you were unable to say it before?
Now it is working so read it.

Regards
Michel

Re: sql problem [message #419811 is a reply to message #419809] Tue, 25 August 2009 12:18 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
My previous post...in the very begining
Re: sql problem [message #419812 is a reply to message #419810] Tue, 25 August 2009 12:19 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
I said ...
Re: sql problem [message #419813 is a reply to message #419794] Tue, 25 August 2009 12:24 Go to previous messageGo to next message
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 #419814 is a reply to message #419813] Tue, 25 August 2009 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and keeps the old value otherwise.

This is NOT skipping the update.

Regards
Michel
Re: sql problem [message #419815 is a reply to message #419814] Tue, 25 August 2009 12:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4643
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 Go to previous messageGo to next message
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!
Re: sql problem [message #419818 is a reply to message #419742] Tue, 25 August 2009 12:42 Go to previous messageGo to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results

IMO, some/many/most posted PL/SQL code in this thread will fail to compile due use of in_salary.
Previous Topic: curly analytics question
Next Topic: Sql Problem (merged 4)
Goto Forum:
  


Current Time: Thu Dec 08 20:36:32 CST 2016

Total time taken to generate the page: 0.08697 seconds