putting updates in main sql

Hi,
I have below set  query which update  PS_POSITION_DATA  table. i want to get rid on update and instead join PS_ACTN_REASON_TBL with PS_POSITION_DATA

select
pos_id,
'' as AG_ACTION_REASON_E
from
PS_POSITION_DATA


i want
select
pos_id,
'' as AG_ACTION_REASON_E
from
PS_POSITION_DATA  a
left outer join  PS_ACTN_REASON_TBL b
on a.POSITION_NBR=b.POSITION_NBR
---can anyone help here to make max(eff_dt) simple






UPDATE PS_AG_POSITIONSTMP
   SET AG_ACTION_REASON_E = (SELECT A.DESCR
                             FROM PS_ACTN_REASON_TBL A
                             WHERE A.ACTION = (SELECT P.ACTION FROM PS_POSITION_DATA P
                                               WHERE P.POSITION_NBR = PS_AG_POSITIONSTMP.POSITION_NBR
                                                 AND P.EFFDT = (SELECT MAX(P1.EFFDT)
                                                                FROM PS_POSITION_DATA P1
                                                                WHERE P1.POSITION_NBR = P.POSITION_NBR
                                                                  AND P1.EFFDT <= $AsOfDate))
                               AND A.ACTION_REASON = PS_AG_POSITIONSTMP.ACTION_REASON
                               AND A.EFFDT = (SELECT MAX(A1.EFFDT)
                                              FROM PS_ACTN_REASON_TBL A1
                                              WHERE A1.ACTION=A.ACTION
                                                AND A1.ACTION_REASON = A.ACTION_REASON
                                               ))
!   WHERE EXISTS (SELECT *                                                                      
   WHERE EXISTS (SELECT 1                                                                      
                 FROM PS_ACTN_REASON_TBL A
                 WHERE A.ACTION = (SELECT P.ACTION FROM PS_POSITION_DATA P
                                   WHERE P.POSITION_NBR = PS_AG_POSITIONSTMP.POSITION_NBR
                                     AND P.EFFDT = (SELECT MAX(P1.EFFDT)
                                                    FROM PS_POSITION_DATA P1
                                                    WHERE P1.POSITION_NBR = P.POSITION_NBR
                                                      AND P1.EFFDT <= $AsOfDate))
                   AND A.ACTION_REASON = PS_AG_POSITIONSTMP.ACTION_REASON
                   AND A.EFFDT = (SELECT MAX(A1.EFFDT)
                                  FROM PS_ACTN_REASON_TBL A1
                                  WHERE A1.ACTION=A.ACTION
                                    AND A1.ACTION_REASON = A.ACTION_REASON
                                    ))
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh DuaCommented:
The query isn't very clear, do you want the update to be modified ? Why ? Would need some clarity on purpose so as to reply accordingly.
sam2929Author Commented:
no want update in select
johnsoneSenior Oracle DBACommented:
You cannot update from within a select statement, so I'm not sure exactly what you are asking for.

This is my guess as to what you are trying to do.  If this isn't it, you are going to need to explain a lot more.
SELECT pos_id, 
       NULL AS ag_action_reason_e 
FROM   (SELECT a.pos_id, 
               b.eff_dat, 
               Row_number() 
                 over ( 
                   PARTITION BY a.pos_id 
                   ORDER BY b.eff_dat DESC) rn 
        FROM   ps_position_data a 
               left outer join ps_actn_reason_tbl b 
                            ON a.position_nbr = b.position_nbr) 
WHERE  rn = 1; 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
I think johnsone is on the right track using ROW_NUMBER instead of all the MAX and EXISTS statements.

Once you get the select figured out, I would look at using MERGE to perform the update:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

If you can provide some sample data and expected results we can probably come up with a working example.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.