how to avoid updating some rows

Hi,

I want to update a cloumn in table with respective to condition status(open,app,exp)

condition is  

1. i want to get max value of one column with the status app,exp
2. and update the max value of column status open,app  alone not exp ?

i am confusing with out updating exp record how can i do that ??
LVL 20
Sathish David  Kumar NArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
>>> with the status app,exp


do you mean it has a status of 'app'  or 'exp'  or it's literally the string 'app,exp'   ?

same question on number 2

and,  what do you want to update to?


plese provide sample data and expected results
0
Sathish David Kumar NArchitectAuthor Commented:
no one column name is status .

eg :

table x

firstname,team,n1, status is are column

eg:

sathish  ML 98 open
kumar    ML  72 app
ravi        ML  22 exp
0
Sathish David Kumar NArchitectAuthor Commented:
if i update the value means

the result should come

sathish  ML 72  open
kumar    ML  72 app
ravi        ML  22 exp

1. i want to get max value of one column with the status app,exp
2. and update the max value of column status open,app  alone not exp ?

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
you repeated verbatim  1 and 2  -  it wasn't helpful the first time you posted it, it doesn't become more clear by repeating it exactly.

>>> with the status app,exp

so ,I'll repeat my question  do you mean it has a status of 'app'  or 'exp'  or it's literally the string 'app,exp'   ?

from your exaple it looks like you want 72 because it's maximum n1 value where status is 'app' or 'exp'

is that correct?
0
sdstuberCommented:
if my guess is correct, then try this...


u pdate yourtable set n1 = (select max(n1) from yourtable where status in ('app','exp'))
where status in ('open','app')
0
Sathish David Kumar NArchitectAuthor Commented:
yes thats correct i got it ...

but it return error message like  sub query return more than one row ?

i want to update all the row which fetch in the sub query .

how can i do that ??
0
sdstuberCommented:
(select max(n1) from yourtable where status in ('app','exp'))

that can't return more than one row.

please post what you tried
0
Sathish David Kumar NArchitectAuthor Commented:
i gave eg sample result only not orignal ..
i put some self joins in the inner query thats is the problem ?

this my orignal query


update EXPORT_DATA  EXP
                    SET TARGET_NO    =( elect              
                     greatest(LD.A,LD.B)
                   
                      FROM EXPORT_DATA LD
                    WHERE LD.EXT_ID  =  EXP.EXT_ID
                    AND   LD.USER_ID     =  EXP.USER_ID    
                    AND   LD.START_DT  =  EXP.START_DT
                    AND   LD.END_DT    =  EXP.END_DT
                   AND  LD.USER_ID ='4KCK5G'
                   and LD.EXT_ID='14K04X'
                    AND   LD.STATUS IN ('APP','EXP'))
       WHERE LD.EXT_ID  =  EXP.EXT_ID
                    AND   LD.USER_ID     =  EXP.USER_ID    
                    AND   LD.START_DT  =  EXP.START_DT
                    AND   LD.END_DT    =  EXP.END_DT
                 
                    AND   LD.STATUS NOT IN ('EXP')
0
sdstuberCommented:
GREATEST isn't the same thing as MAX

why did you change it?
0
Sathish David Kumar NArchitectAuthor Commented:
because i want to find more than one column

greatest(LD.A,LD.B)
0
sdstuberCommented:
then use both,  MAX is essential for your query so you return a single value


max(greatest(ld.a,ld.b))
0
Sathish David Kumar NArchitectAuthor Commented:
how to aviod that  sub query return more than one row
0
sdstuberCommented:
to be safe,  you might want to include NVL  if either A or B might be NULL


max(greatest(nvl(ld.a,ld.b),nvl(ld.b,ld.a))
0
Sathish David Kumar NArchitectAuthor Commented:
why we want to use both ??

what is greatest method do ?????
0
Sathish David Kumar NArchitectAuthor Commented:
No its must have value ...

its working thanks ...  but i want to know what is greatest method do ?????
0
sdstuberCommented:
greatest compares two values and returns the bigger one

greatest(1,2)  returns 2
greatest(2,1) returns 2


max() returns the biggest value within a column across multiple rows

so if you have the following values...

X,Y
----
1,2
3,4
5,6
6,4
5,3
4,2

max(greatest(x,y)) will return 6
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.