Sathish David Kumar N
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 ??
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 ??
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
eg :
table x
firstname,team,n1, status is are column
eg:
sathish ML 98 open
kumar ML 72 app
ravi ML 22 exp
ASKER
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 ?
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?
>>> 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')
u pdate yourtable set n1 = (select max(n1) from yourtable where status in ('app','exp'))
where status in ('open','app')
ASKER
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 ??
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
that can't return more than one row.
please post what you tried
ASKER
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')
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?
why did you change it?
ASKER
because i want to find more than one column
greatest(LD.A,LD.B)
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))
max(greatest(ld.a,ld.b))
ASKER
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))
max(greatest(nvl(ld.a,ld.b
ASKER
why we want to use both ??
what is greatest method do ?????
what is greatest method do ?????
ASKER
No its must have value ...
its working thanks ... but i want to know what is greatest method do ?????
its working thanks ... but i want to know what is greatest method do ?????
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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