Sathish David Kumar N
asked on
How to update a row ?
HI i have data like this
Name group n1 n2 n3 n4 g1
Sathish n1 4 5 6 7 4 according to group g1 value will update
kumar n2 5 7 8 9 7
Sathish n1 6 8 9 19 6
i want to run some query if same user name exits then
i want get the max value that group and update in g1 .
how to get this ??
Name group n1 n2 n3 n4 g1
Sathish n1 4 5 6 7 4 according to group g1 value will update
kumar n2 5 7 8 9 7
Sathish n1 6 8 9 19 6
i want to run some query if same user name exits then
i want get the max value that group and update in g1 .
how to get this ??
if my guess is correct then try this....
update yourtable a set g1 = (select max(g1) from yourtable b where a.name = b.name)
update yourtable a set g1 = (select max(g1) from yourtable b where a.name = b.name)
ASKER
s correct output.
but some times gruop may be diffrent . so depends on gruop only i want to update
but some times gruop may be diffrent . so depends on gruop only i want to update
>but some times gruop may be diffrent
if the update above doesn't do what you're looking for, you'll need to be more specific.
provide sample input data and expected result data that demonstrates the grouping rules you're interested in
if the update above doesn't do what you're looking for, you'll need to be more specific.
provide sample input data and expected result data that demonstrates the grouping rules you're interested in
ASKER
Name group n1 n2 n3 n4 g1
Sathish n1 4 5 6 7 4 according to group g1 value will update
kumar n2 5 7 8 9 7
Sathish n3 6 8 9 19 6
Sathish n1 4 5 6 7 4
kumar n2 5 7 8 9 7
Sathish n4 6 8 9 19 6
like this
Sathish n1 4 5 6 7 4 according to group g1 value will update
kumar n2 5 7 8 9 7
Sathish n3 6 8 9 19 6
Sathish n1 4 5 6 7 4
kumar n2 5 7 8 9 7
Sathish n4 6 8 9 19 6
like this
what is that?
is that input - what is the output?
if output - what is the input?
"according to group g1 value will update" - I have no idea what you are trying to say.
"according to group" - according to what? what distinguishes this?
g1 value will update to what?
here's another guess
update yourtable a set g1 = (select max(g1) from yourtable b where a.groupcolumn = b.groupcolumn)
again, if this doesn't work please provide COMPLETE example. both input AND output
is that input - what is the output?
if output - what is the input?
"according to group g1 value will update" - I have no idea what you are trying to say.
"according to group" - according to what? what distinguishes this?
g1 value will update to what?
here's another guess
update yourtable a set g1 = (select max(g1) from yourtable b where a.groupcolumn = b.groupcolumn)
again, if this doesn't work please provide COMPLETE example. both input AND output
ASKER
output is
Sathish n1 4 5 6 7 4 according to group g1 value will update
kumar n2 5 7 8 9 7
Sathish n3 6 8 9 19 6
Sathish n1 4 5 6 7 6
kumar n2 5 7 8 9 7
Sathish n4 6 8 9 19 6
Sathish n1 4 5 6 7 4 according to group g1 value will update
kumar n2 5 7 8 9 7
Sathish n3 6 8 9 19 6
Sathish n1 4 5 6 7 6
kumar n2 5 7 8 9 7
Sathish n4 6 8 9 19 6
" according to group g1 value will update "
you've said that 3 times, it still doesn't help.
in fact, I'm more confused now
This is your input
Sathish n1 4 5 6 7 4 according to group g1 value will update
This is your output
Sathish n1 4 5 6 7 4 according to group g1 value will update
your expected output shows that line with NO update.
Are you sure your example is correct?
If it is, please explain in more detail "according to group" is not helpful, try explaining a different way
you've said that 3 times, it still doesn't help.
in fact, I'm more confused now
This is your input
Sathish n1 4 5 6 7 4 according to group g1 value will update
This is your output
Sathish n1 4 5 6 7 4 according to group g1 value will update
your expected output shows that line with NO update.
Are you sure your example is correct?
If it is, please explain in more detail "according to group" is not helpful, try explaining a different way
ASKER
>>>>"according to group g1 value will update" - I have no idea what you are trying to say.
"according to group" - according to what? what distinguishes this?
Sorry jsut i am copy paste thats y its coming .
Let me explain clearly
Name group n1 n2 n3 n4 g1
Sathish n1 4 5 6 7
kumar n2 5 7 8 9
Sathish n3 6 8 9 19
Sathish n1 4 5 6 7
kumar n2 5 7 8 9
Sathish n4 6 8 9 19
this is input,
here 4 sathish and 2 kumar are there but group are some what diffrent in sathish case . only 2 group are equal
so 1st want to check name after that group
eg sathish n1
so i want take values in n1 and update in g1 but
sathish and group n1 repeat then i want to get max value of that .
so output coming like this
Sathish n1 6 5 6 7 4
kumar n2 5 7 8 9 7
Sathish n3 6 8 9 19 6
Sathish n1 4 5 6 7 6
kumar n2 5 7 8 9 7
Sathish n4 6 8 9 19 6
"according to group" - according to what? what distinguishes this?
Sorry jsut i am copy paste thats y its coming .
Let me explain clearly
Name group n1 n2 n3 n4 g1
Sathish n1 4 5 6 7
kumar n2 5 7 8 9
Sathish n3 6 8 9 19
Sathish n1 4 5 6 7
kumar n2 5 7 8 9
Sathish n4 6 8 9 19
this is input,
here 4 sathish and 2 kumar are there but group are some what diffrent in sathish case . only 2 group are equal
so 1st want to check name after that group
eg sathish n1
so i want take values in n1 and update in g1 but
sathish and group n1 repeat then i want to get max value of that .
so output coming like this
Sathish n1 6 5 6 7 4
kumar n2 5 7 8 9 7
Sathish n3 6 8 9 19 6
Sathish n1 4 5 6 7 6
kumar n2 5 7 8 9 7
Sathish n4 6 8 9 19 6
ASKER
sorry 1st
should be
Sathish n1 6 5 6 7 6
should be
Sathish n1 6 5 6 7 6
I still think your example is wrong
shouldn't the last line of your output be....
Sathish n4 6 8 9 19 19
and third line of output
Sathish n3 6 8 9 19 9
shouldn't the last line of your output be....
Sathish n4 6 8 9 19 19
and third line of output
Sathish n3 6 8 9 19 9
ASKER
s your correct ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what is decode function do??
ASKER
i jsut run the select query nothing is giving
SELECT MAX(DECODE(grp, 'n1', n1, 'n2', n2, 'n3', n3, 'n4', n4))
FROM yourtable b,yourtable a
WHERE a.grp = b.grp
a.name=a.name
can you tell me clearly
n1 is my column name . what is first n1
SELECT MAX(DECODE(grp, 'n1', n1, 'n2', n2, 'n3', n3, 'n4', n4))
FROM yourtable b,yourtable a
WHERE a.grp = b.grp
a.name=a.name
can you tell me clearly
n1 is my column name . what is first n1
I'll arrange the decode to make it easier to see the pattern
decode looks that first parameter and then, working in pairs, if it matches the first value in a pair, it returns the second value.
it searches until it finds a match or walks off the end of the list. If no match it returns NULL
DECODE(grp,
'n1', n1, --- if grp = 'n1' then return the value in column n1
'n2', n2, --- if grp = 'n2' then return the value in column n2
'n3', n3, --- if grp = 'n3' then return the value in column n3
'n4', n4 --- if grp = 'n4' then return the value in column n4
) --- if grp isn't any of the strings, then return null
decode looks that first parameter and then, working in pairs, if it matches the first value in a pair, it returns the second value.
it searches until it finds a match or walks off the end of the list. If no match it returns NULL
DECODE(grp,
'n1', n1, --- if grp = 'n1' then return the value in column n1
'n2', n2, --- if grp = 'n2' then return the value in column n2
'n3', n3, --- if grp = 'n3' then return the value in column n3
'n4', n4 --- if grp = 'n4' then return the value in column n4
) --- if grp isn't any of the strings, then return null
ASKER
its update only 1st condition not in the 2 condition thats means
like that its come
Sathish n1 6 5 6 7 8
Sathish n1 4 5 6 7 4
Sathish n1 8 9 6 9 8
like that its come
Sathish n1 6 5 6 7 8
Sathish n1 4 5 6 7 4
Sathish n1 8 9 6 9 8
is that what you want, or what you get?
if it's what you want, please explain what rules determine a row gets updated or not.
don't say "first row" unless there is a way to determine which row is "first", and if there is such a way, please explain what it is
if those results are what you are getting but not what you want,
please post the input data that produced those results and the exact query you tried
if it's what you want, please explain what rules determine a row gets updated or not.
don't say "first row" unless there is a way to determine which row is "first", and if there is such a way, please explain what it is
if those results are what you are getting but not what you want,
please post the input data that produced those results and the exact query you tried
also, if there are ANY other deviations, please post
INPUT AND expected OUTPUT (don't send me output you don't want, only send output you do want)
do both, in one post, don't just list one, then we have to have another round of me trying to guess what you intended.
and, make sure your input/output example is complete and accurate.
I responded to the original question with an answer in 3 minutes, but the back-and-forth of missing/erroneous information has taken another 2+ hours.
Your question is probably simple to answer given a good example that shows what you really want.
INPUT AND expected OUTPUT (don't send me output you don't want, only send output you do want)
do both, in one post, don't just list one, then we have to have another round of me trying to guess what you intended.
and, make sure your input/output example is complete and accurate.
I responded to the original question with an answer in 3 minutes, but the back-and-forth of missing/erroneous information has taken another 2+ hours.
Your question is probably simple to answer given a good example that shows what you really want.
ASKER
>>>>is that what you want, or what you get?
No i want out put like this
Sathish n1 6 5 6 7 8
Sathish n1 4 5 6 7 8
Sathish n1 8 9 6 9 8
but output coming like below only its update the 1st row only not other rows thats the problem
Sathish n1 6 5 6 7 8
Sathish n1 4 5 6 7 4
Sathish n1 8 9 6 9 8
No i want out put like this
Sathish n1 6 5 6 7 8
Sathish n1 4 5 6 7 8
Sathish n1 8 9 6 9 8
but output coming like below only its update the 1st row only not other rows thats the problem
Sathish n1 6 5 6 7 8
Sathish n1 4 5 6 7 4
Sathish n1 8 9 6 9 8
what statement did you use?
what data was your input?
what data was your input?
The update statement in http:#37084796
has no row-criteria in it, it will update every row in a group, not just one of them
if you altered that statement then your version might not be corrrect
please post what you actually ran
has no row-criteria in it, it will update every row in a group, not just one of them
if you altered that statement then your version might not be corrrect
please post what you actually ran
ASKER
>>>>>what statement did you use?
I am running the update query from java class , prepared satement i am using
>>>>>what data was your input?
nothing i will give jsut run update query my original query is
update LEAD_DATA EXP
SET TARGET_OF_CALL =( select
MAX(DECODE( LD.CATEGORY, 'A', LD.A,
'B', LD.B,
'C', LD.C,
'D', LD.D))
FROM LEAD_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
) Where STATUS NOT IN ('APP','EXP') ;
thats it
Its not update the full condition
am i did anything wrong in query
I am running the update query from java class , prepared satement i am using
>>>>>what data was your input?
nothing i will give jsut run update query my original query is
update LEAD_DATA EXP
SET TARGET_OF_CALL =( select
MAX(DECODE( LD.CATEGORY, 'A', LD.A,
'B', LD.B,
'C', LD.C,
'D', LD.D))
FROM LEAD_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
) Where STATUS NOT IN ('APP','EXP') ;
thats it
Its not update the full condition
am i did anything wrong in query
ASKER
the same user id ,ext_id,start date and end have 40 records all are in status 'start'
its not updating 40 records,
in that table have status 'start' have 1600 records
but it update only 978 only.
if i seprately run add other condition like group then it will increase to 1248
thats only i am confusing
its not updating 40 records,
in that table have status 'start' have 1600 records
but it update only 978 only.
if i seprately run add other condition like group then it will increase to 1248
thats only i am confusing
ASKER
Sorry its my mistake thanks ...
ASKER
Have you see my Query ?? am i did anything wrong ? is any input for that ?
Name group n1 n2 n3 n4 g1
Sathish n1 4 5 6 7 4
kumar n2 5 7 8 9 7
Sathish n1 6 8 9 19 6
you want like this then below query will work
Name group n1 n2 n3 n4 g1
Sathish n1 4 5 6 7 6
kumar n2 5 7 8 9 7
Sathish n1 6 8 9 19 6
update tablename a set g1 = (select max(n1) from tablename b where a.name = b.name)
is this what you want the results to be?
Sathish n1 4 5 6 7 6
kumar n2 5 7 8 9 7
Sathish n1 6 8 9 19 6