Link to home
Start Free TrialLog in
Avatar of Sathish David  Kumar N
Sathish David Kumar NFlag for India

asked on

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 ??
Avatar of Sean Stuber
Sean Stuber

>>> 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
Avatar of Sathish David  Kumar N

ASKER

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
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 ?

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?
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')
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 ??
(select max(n1) from yourtable where status in ('app','exp'))

that can't return more than one row.

please post what you tried
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')
GREATEST isn't the same thing as MAX

why did you change it?
because i want to find more than one column

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


max(greatest(ld.a,ld.b))
how to aviod that  sub query return more than one row
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))
why we want to use both ??

what is greatest method do ?????
No its must have value ...

its working thanks ...  but i want to know what is greatest method do ?????
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial