Update Query

I'm having quite the day, I'm unable to turn this access query into a pl/sql update query.

Please help :)
UPDATE CDB.PRJ_LM TIE 
    SET REC_CHNG_CD = 'U',
    MODIFIED_BY_ID = PARAM_0,
    MODIFIED_DT = SYSDATE,
    STAT_CD = 'I'
    WHERE EXISTS (SELECT T.P_ID P_ID, TIE.LM_ID ID, T.NM NAME,
'N/A' DEF_TX, FROM CDB.PRJ_T T LEFT JOIN CDB.PRJ_LM TIE ON (T.PUB_ID = TIE.PUB_ID) 
AND (T.ID = TIE.ID)
WHERE (((T.P_ID)= 1) 
AND ((T.CHNG_CD)='U') 
AND ((T.TMPLT_STAT_CD)='I') 
AND ((TIE.TIELM_STAT_CD)='A'))

Open in new window

lulubell-bAsked:
Who is Participating?
 
hraja77Commented:
UPDATE CDB.PRJ_LM TIE
    SET REC_CHNG_CD = 'U',
    MODIFIED_BY_ID = PARAM_0,
    MODIFIED_DT = SYSDATE,
    STAT_CD = 'I'
    WHERE EXISTS (SELECT T.P_ID P_ID, TIE.LM_ID ID, T.NM NAME,
'N/A' DEF_TX, FROM CDB.PRJ_T T where (T.PUB_ID = TIE.PUB_ID
AND T.ID = TIE.ID)
WHERE (((T.P_ID)= 1)
AND ((T.CHNG_CD)='U')
AND ((T.TMPLT_STAT_CD)='I')
AND ((TIE.TIELM_STAT_CD)='A'))
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
There are lot of parenthesis missing/incorrect in the above version of your query. So corrected the same.

Try this to see if it works for you.

UPDATE CDB.PRJ_LM TIE
    SET REC_CHNG_CD = 'U',
    MODIFIED_BY_ID = PARAM_0,
    MODIFIED_DT = SYSDATE,
    STAT_CD = 'I'
WHERE EXISTS (SELECT 1 FROM CDB.PRJ_T T
WHERE T.PUB_ID = TIE.PUB_ID
AND T.ID = TIE.ID
and T.P_ID= 1
AND T.CHNG_CD='U'
AND T.TMPLT_STAT_CD='I'
AND TIE.TIELM_STAT_CD='A' )
0
 
Aaron ShiloChief Database ArchitectCommented:
1. you had 1 " , " in you inner query just before the FROM key word.
2. when using EXIST no need to retrive all these column you will not use them your just checking for an existance of a value SO i changed that to a constant " 1 ".

enjoy .

UPDATE CDB.PRJ_LM TIE
    SET REC_CHNG_CD = 'U',
    MODIFIED_BY_ID = PARAM_0,
    MODIFIED_DT = SYSDATE,
    STAT_CD = 'I'
    WHERE EXISTS (SELECT1 FROM CDB.PRJ_T T LEFT JOIN CDB.PRJ_LM TIE ON (T.PUB_ID = TIE.PUB_ID)
AND (T.ID = TIE.ID)
WHERE (((T.P_ID)= 1)
AND ((T.CHNG_CD)='U')
AND ((T.TMPLT_STAT_CD)='I')
AND ((TIE.TIELM_STAT_CD)='A'))
0
 
lulubell-bAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.