?
Solved

how to avoid updating some rows

Posted on 2011-10-31
16
Medium Priority
?
250 Views
Last Modified: 2012-05-12
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 ??
0
Comment
  • 8
  • 8
16 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37057288
>>> 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
 
LVL 20

Author Comment

by:Sathish David Kumar N
ID: 37057333
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
 
LVL 20

Author Comment

by:Sathish David Kumar N
ID: 37057346
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 74

Expert Comment

by:sdstuber
ID: 37057366
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37057391
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
 
LVL 20

Author Comment

by:Sathish David Kumar N
ID: 37057822
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37057905
(select max(n1) from yourtable where status in ('app','exp'))

that can't return more than one row.

please post what you tried
0
 
LVL 20

Author Comment

by:Sathish David Kumar N
ID: 37058020
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37058028
GREATEST isn't the same thing as MAX

why did you change it?
0
 
LVL 20

Author Comment

by:Sathish David Kumar N
ID: 37058783
because i want to find more than one column

greatest(LD.A,LD.B)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37058820
then use both,  MAX is essential for your query so you return a single value


max(greatest(ld.a,ld.b))
0
 
LVL 20

Author Comment

by:Sathish David Kumar N
ID: 37058827
how to aviod that  sub query return more than one row
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37058830
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
 
LVL 20

Author Comment

by:Sathish David Kumar N
ID: 37058848
why we want to use both ??

what is greatest method do ?????
0
 
LVL 20

Author Comment

by:Sathish David Kumar N
ID: 37058859
No its must have value ...

its working thanks ...  but i want to know what is greatest method do ?????
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37062581
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question