CODEWITHSUNDEEP
sqloracle-database
B. S. Rawat
B. S. Rawat

Reputation: 1914

Update Field in Join table

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!

Upvotes: 2

Views: 899

Answers (5)

Dinesh
Dinesh

Reputation: 41

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)

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132580

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.

Upvotes: 0

Eric
Eric

Reputation: 95133

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.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425341

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.

Upvotes: 1

Aaron Alton
Aaron Alton

Reputation: 23226

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'

Upvotes: 0

Related Questions