We help IT Professionals succeed at work.

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

 
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I see your input,  you want g1 to be updated, what is  the output?


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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
if my guess is correct then try this....


update yourtable a set g1 = (select max(g1) from yourtable b where a.name = b.name)
CERTIFIED EXPERT

Author

Commented:
s correct output.

but some times gruop may be diffrent . so depends on gruop only i want to update
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>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
CERTIFIED EXPERT

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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





CERTIFIED EXPERT

Author

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
"  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
CERTIFIED EXPERT

Author

Commented:
>>>>"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



CERTIFIED EXPERT

Author

Commented:
sorry 1st
should be

Sathish        n1              6           5       6    7     6
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
CERTIFIED EXPERT

Author

Commented:
s your correct ...
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
U PDATE yourtable a
   SET g1      =
           (SELECT MAX(DECODE(grp,  'n1', n1,  'n2', n2,  'n3', n3,  'n4', n4))
              FROM yourtable b
             WHERE a.grp = b.grp);
CERTIFIED EXPERT

Author

Commented:
what is decode function do??
CERTIFIED EXPERT

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
CERTIFIED EXPERT

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.
CERTIFIED EXPERT

Author

Commented:
>>>>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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
what statement did you use?

what data was your input?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
CERTIFIED EXPERT

Author

Commented:
>>>>>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
CERTIFIED EXPERT

Author

Commented:
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
 
CERTIFIED EXPERT

Author

Commented:
Sorry its my mistake thanks ...
CERTIFIED EXPERT

Author

Commented:
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)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.