Oracle update columns with Max value from same table and partial update on column

Question

I have to update multiple columns with max value on the same table. A pseudo query will be like -

UPDATE PERMIT 
SET P_ID = 'FIRST FIVE CHARACTERS WILL REMAIN SAME AND LAST few WILL BE REPLACED BY SELECT Max(prmt_appl_sq) FROM PERMIT SOME CONDITION',
prmt_appl_sq = 'AND NOW UPDATE WITH SAME MAX VALUE USED ABOVE'
WHERE 
CONDITION

table data is like P_ID P_APPL_SQ CCode 2016-H-193-5 10 193 There is a unique constraint on column P_ID and the last part ***-5 which actually is value in P_APPL_SQ gets incremented every time a record is created with same other three values. for e.g when a new record is created having yr 2016 and type -H and Center 193 the P_ID will become ********-6. I have to update all the records having CCode =193 to 195 and update p_ID to 2016-H-195-"MAX value +1 for the combination of first three" and then also update P_APPL_SQ with same value.

I came up with the query but it is trying add same value on each row thus throwing unique constraint violation
UPDATE OSPDBA.OSPT001_PRMT_APPL
SET rted_to_org_cd = '55014040191', PRMT_ID = SUBSTR(PRMT_ID, 0, 11) || (SELECT Max(prmt_appl_sq) + 1 FROM OSPDBA.OSPT001_PRMT_APPL where rted_to_org_cd = '55014040191' and prmt_sbmt_yr = 2016 and prmt_typ_cd = 'H'), prmt_appl_sq = ((SELECT Max(prmt_appl_sq) + 1 FROM OSPDBA.OSPT001_PRMT_APPL where rted_to_org_cd = '55014040191' and prmt_sbmt_yr = 2016 and prmt_typ_cd = 'H')) WHERE rted_to_org_cd IN ('55014040193','55014040195') and prmt_sbmt_yr = 2016;

How can we have unique max value for each record?

Most problems like this CAN be solved with a single SQL statement. The main requirement is that the problem statement be logically consistent. (Otherwise the problem can't be solved by ANY means.) For more help, though, you will need to provide table structure (column names and data types, only for the columns needed for the problem), some sample data that illustrates all the possible complications and special cases, and desired output. — , Dec 13 '16 at 17:35
This is [definitely possible](http://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table); have you tried googling first? Also, when in a syntactic doubt, [reading Oracle's SQL reference](https://docs.oracle.com/cloud/latest/db112/SQLRF/statements_10008.htm) is usually a good idea. — 9000, Dec 13 '16 at 17:43

Leonid Alzhin · Answer 1 · 2016-12-13T19:41:42.963

0

It can be done in a single query, no problem. A pseudo query closer to the actual query would be

UPDATE PERMIT 
SET P_ID = substr(p_id, 0, 5) || (SELECT Max(prmt_appl_sq) FROM PERMIT SOME CONDITION),
prmt_appl_sq = (SELECT Max(prmt_appl_sq) FROM PERMIT SOME CONDITION)
WHERE 
CONDITION

The condition must be well design though, if PERMIT is big enough you might run into performance issues.

=======================================================

Updated query

UPDATE OSPDBA.OSPT001_PRMT_APPL a 

SET rted_to_org_cd = '55014040191', 

PRMT_ID = SUBSTR(PRMT_ID, 0, 11) || (SELECT Max(prmt_appl_sq) + 1 FROM OSPDBA.OSPT001_PRMT_APPL b where a.rted_to_org_cd = b.rted_to_org_cd and prmt_sbmt_yr = 2016 and prmt_typ_cd = 'H'), 

prmt_appl_sq = (SELECT Max(prmt_appl_sq) + 1 FROM OSPDBA.OSPT001_PRMT_APPL c where a.rted_to_org_cd = c.rted_to_org_cd and prmt_sbmt_yr = 2016 and prmt_typ_cd = 'H') 

WHERE rted_to_org_cd IN ('55014040193','55014040195') and prmt_sbmt_yr = 2016;

edited Dec 13 '16 at 19:41

answered Dec 13 '16 at 18:20

Leonid Alzhin

164
2
8

I tried this query but running into unique constraint violation since it tries to update prmt_appl_sq with same value for each record. – joshig Dec 13 '16 at 19:03
didn't you say "unique constraint on column P_ID"? If prmt_appl_sq is the unique constraint then you need to make it unique, like max(prmt_appl_sq) + 1 for example... – Leonid Alzhin Dec 13 '16 at 19:07
Yes Its on P_ID. But it is trying to append same max value on each record which is making the value duplicate. The query works if you just run it for one record. – joshig Dec 13 '16 at 19:12
UPDATE OSPDBA.OSPT001_PRMT_APPL SET rted_to_org_cd = '55014040191', PRMT_ID = SUBSTR(PRMT_ID, 0, 11) || (SELECT Max(prmt_appl_sq) + 1 FROM OSPDBA.OSPT001_PRMT_APPL where rted_to_org_cd = '55014040191' and prmt_sbmt_yr = 2016 and prmt_typ_cd = 'H'), prmt_appl_sq = ((SELECT Max(prmt_appl_sq) + 1 FROM OSPDBA.OSPT001_PRMT_APPL where rted_to_org_cd = '55014040191' and prmt_sbmt_yr = 2016 and prmt_typ_cd = 'H')) WHERE prmt_sq in (19246); -- it works but gets error if you run it for below condition --rted_to_org_cd IN ('55014040193','55014040195') and prmt_sbmt_yr = 2016; – joshig Dec 13 '16 at 19:14
Ok, I put the formatted query in my answer since here all formatting is stripped. I guess you'll need to modify SET rted_to_org_cd = '55014040191' to something dynamic. – Leonid Alzhin Dec 13 '16 at 19:33
still getting same error. may be i have to write a cursor. – joshig Dec 13 '16 at 20:24

Amazigh.Ca · Answer 2 · 2016-12-13T18:35:43.793

0

Try this query:

UPDATE P1
SET P1.P_ID = SUBSTRING(P2.P_ID,1,5) ||''|| P2.MaxValue, 
P1.prmt_appl_sq = P2.MaxValue
FROM PERMIT P1 INNER JOIN 
( select P_ID, MAX(prmt_appl_sq) OVER() AS MaxValue from  PERMIT where some condition) As P2
where P1.P_ID = P2.P_ID and some condition

edited Dec 13 '16 at 18:35

answered Dec 13 '16 at 18:21

Amazigh.Ca

2,753
2
11
8

getting syntax error UPDATE P1 SET P1.rted_to_org_cd = '55014040191', P1.PRMT_ID = SUBSTRING(P2.PRMT_ID,0,11) || P2.MaxValue, P1.prmt_appl_sq = P2.MaxValue FROM OSPDBA.OSPT001_PRMT_APPL P1 INNER JOIN ( select prmt_sq, MAX(prmt_appl_sq) OVER() AS MaxValue from OSPDBA.OSPT001_PRMT_APPL where rted_to_org_cd = '55014040191' and prmt_sbmt_yr = 2016 and prmt_typ_cd = 'H') As P2 where P1.prmt_sq = P2.prmt_sq and rted_to_org_cd IN ('55014040193','55014040195') and prmt_sbmt_yr = 2016; – joshig Dec 13 '16 at 19:15

score 0 · Answer 3 · answered Dec 14 '16 at 21:58

I have to write a cursor to do the batch updates.

    DECLARE
    C_PRMT_SQ                   PRMT_APPL.PRMT_SQ%TYPE; 
    C_PRMT_SBMT_YR              PRMT_APPL.PRMT_SBMT_YR%TYPE;    
    MAX_APPL_SEQ                PRMT_APPL.PRMT_APPL_SQ%TYPE;    

    L_ERR_LEVEL                 NUMBER := 0;
    MAX_SEQ_VAL                 VARCHAR2(220);


    -- SELECT ALL PERMITS OF PURGED CC FROM THE DATABASE
    CURSOR CSEQUENCE IS
        SELECT PRMT_SQ,PRMT_SBMT_YR FROM PRMT_APPL 
        WHERE RTED_TO_ORG_CD IN ('55014040193','55014040195') ORDER BY PRMT_SBMT_YR DESC;
-- MAIN 
BEGIN
    OPEN CSEQUENCE; 
    L_ERR_LEVEL := 1;

    LOOP

    FETCH CSEQUENCE INTO C_PRMT_SQ,C_PRMT_SBMT_YR;

    DBMS_OUTPUT.PUT_LINE ('C_PRMT_SQ ' || TO_CHAR(C_PRMT_SQ) || '  '  || TO_CHAR(C_PRMT_SBMT_YR));

    EXIT WHEN CSEQUENCE%NOTFOUND;

    SELECT (MAX(PRMT_APPL_SQ) + 1) INTO MAX_APPL_SEQ FROM PRMT_APPL WHERE  RTED_TO_ORG_CD = '55014040191' AND PRMT_SBMT_YR = C_PRMT_SBMT_YR AND PRMT_TYP_CD = 'H'; 

    MAX_SEQ_VAL := 'MAX_APPL_SEQ ' || TO_CHAR(MAX_APPL_SEQ); 

    DBMS_OUTPUT.PUT_LINE (MAX_SEQ_VAL);

    UPDATE PRMT_APPL  
        SET   
            RTED_TO_ORG_CD = '55014040191',
            PRMT_ID = SUBSTR(PRMT_ID, 0, 7) || 191 || '-' || MAX_APPL_SEQ,
            PRMT_APPL_SQ = MAX_APPL_SEQ,
            UPDT_TMS = SYSTIMESTAMP
        WHERE   PRMT_SQ = C_PRMT_SQ;

    COMMIT;

    END LOOP;
    CLOSE CSEQUENCE;    
    L_ERR_LEVEL := 3;
END;

Oracle update columns with Max value from same table and partial update on column

3 Answers3