Update Field in Join table

Question

Hi Experts,

I have a select query as

SELECT MFD.MONTHLY_FUND_HEADER_ID
FROM MONTHLY_FUND_DETAIL MFD, MONTHLY_FUND_HEADER MFH
WHERE
    MFH.MONTH < TO_CHAR(TRUNC(sysdate), 'MM')
    AND MFH.YEAR <= TO_CHAR(TRUNC(sysdate), 'YYYY')
    AND MFH.CERTIFIED_DT IS NOT NULL
    AND MFD.SENT_TO_EXT_IND = 'N' AND MFH.COURT_CD = '56'
GROUP BY MFD.MONTHLY_FUND_HEADER_ID

Now I want an update query which will set MFD.SENT_TO_EXT_IND field to 'Y' for above join

Please suggest the query.

Thanks!

Answer 1

The update clause works with a subquery in Oracle. So, you can do this:

UPDATE
(SELECT
    mfd.sent_to_ext_ind
FROM 
    MONTHLY_FUND_DETAIL MFD, 
    MONTHLY_FUND_HEADER MFH 
WHERE 
    (MFH.MONTH < TO_CHAR(TRUNC(sysdate),'MM') 
    AND MFH.YEAR <=TO_CHAR(TRUNC(sysdate) , 'YYYY')) 
    AND MFH.CERTIFIED_DT IS NOT NULL 
    AND MFD.SENT_TO_EXT_IND = 'N' 
    AND MFH.COURT_CD= '56') mfd
 SET sent_to_ext_ind = 'Y'

Just make sure that the table you're updating is the first table in the FROM clause. You shouldn't have to modify your query to much.

It should be noted that this is not ANSI, but it does work with Oracle. Since you're using Oracle, you can take advantage of it!

Note that this doesn't actually have a join condition...there should be a MFD.FUNDID = MFH.FUNDID or similar clause in the WHERE condition. Otherwise, you are going a very roundabout way of setting all N values to Y.

Answer 2

I think you're missing "FROM" prior to MONTHLY_FUND_DETAIL on the first line. If so, this is one way to perform the update that you're looking to do:

UPDATE MFDT
SET MFDT.SENT_TO_EXT_IND = 'Y'
FROM MONTHLY_FUND_DETAIL MFDT
JOIN MONTHLY_FUND_HEADER MFH ON MFDT.MONTHLY_FUND_HEADER_ID = MFH.MONTHLY_FUND_HEADER_ID
WHERE (MFH.MONTH < TO_CHAR(TRUNC(sysdate),'MM') 
    AND MFH.YEAR <=TO_CHAR(TRUNC(sysdate) , 'YYYY')) 
    AND MFH.CERTIFIED_DT IS NOT NULL 
    AND MFD.SENT_TO_EXT_IND = 'N' 
    AND MFH.COURT_CD= '56'

Answer 3

Your query won't compile, since it misses FROM clause.

If we add it, we'll see that the query is a CROSS JOIN:

SELECT  MFD.MONTHLY_FUND_HEADER_ID
FROM    MONTHLY_FUND_DETAIL MFD, MONTHLY_FUND_HEADER MFH
WHERE   MFH.MONTH < TO_CHAR(TRUNC(sysdate),'MM'
        AND MFH.YEAR <= TO_CHAR(TRUNC(sysdate) , 'YYYY'))
        AND MFH.CERTIFIED_DT IS NOT NULL
        AND MFH.COURT_CD = '56'
        AND MFD.SENT_TO_EXT_IND = 'N'
GROUP BY
        MFD.MONTHLY_FUND_HEADER_ID

, so you are asking about updating all rows of MONTHLY_FUND_DETAIL that currently hold an 'N':

UPDATE  MONTHLY_FUND_DETAIL
SET     SENT_TO_EXT_IND = 'Y'
WHERE   SENT_TO_EXT_IND = 'N'

Not sure, though, that it's what you want.

Add a JOIN condition into your query so that it makes sense.

Answer 4

This should work:

update
( select mfd.sent_to_ext_ind 
  from   monthly_fund_detail mfd, 
         monthly_fund_header mfh 
  where (mfh.month < to_char(trunc(sysdate),'MM') 
  and mfh.year <=to_char(trunc(sysdate) , 'YYYY')) 
  and mfh.certified_dt is not null 
  and mfd.sent_to_ext_ind = 'N' and mfh.court_cd= '56' 
)
set sent_to_ext_ind = 'Y';

Contrary to other answers, Oracle does not support "update from" syntax.

Answer 5

You can use following update statement

update MONTHLY_FUND_DETAIL 
set SENT_TO_EXT_IND  = 'Y'
where MONTHLY_FUND_HEADER_ID in (
SELECT MFD.MONTHLY_FUND_HEADER_ID
FROM MONTHLY_FUND_DETAIL MFD, MONTHLY_FUND_HEADER MFH
WHERE
    MFH.MONTH < TO_CHAR(TRUNC(sysdate), 'MM')
    AND MFH.YEAR <= TO_CHAR(TRUNC(sysdate), 'YYYY')
    AND MFH.CERTIFIED_DT IS NOT NULL
    AND MFD.SENT_TO_EXT_IND = 'N' AND MFH.COURT_CD = '56'
GROUP BY MFD.MONTHLY_FUND_HEADER_ID)