Update query (first time for everyone)....

goldfishhh · #1 (**permalink**) 08-13-10, 12:58

Not quite sure what illegal symbol I'm using. I'm pretty sure this query should work to replace the bad applctn_txt with the correct one.

Code:

---------+---------+---------+---------+---------+---------+---------+---------
UPDATE                                                                         
  SET PA.APPLCTN_TXT = JX.FULL_SYS_NM_TXT                                      
FROM TOTDB01.PROD_ABEND PA                                                     
  INNER JOIN TOTDB01.JOB_XREF JX                                               
  ON SUBSTR(PA.JOB_TXT,1,2) = JX.PFX_TXT                                       
WHERE PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130;                  
---------+---------+---------+---------+---------+---------+---------+---------
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT   
         BE LEGAL ARE: INCLUDE <END-OF-STATEMENT> QUERYNO WHERE SKIP WITH SET  
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 3 0  0  -1  81  502 SQL DIAGNOSTIC INFORMATION           
DSNT416I SQLERRD    = X'00000003'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000051'  X'000001F6' SQL DIAGNOSTIC INFORMATION                   
---------+---------+---------+---------+---------+---------+---------+---------
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0                                      
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

Marcus_A · #2 (**permalink**) 08-13-10, 13:38

You need the table name right after the UPDATE.

goldfishhh · #3 (**permalink**) 08-13-10, 13:52

Uhh

Code:

---------+---------+---------+---------+---------+---------+---------+---------
UPDATE TOTDB01.PROD_ABEND PA                                                   
  SET PA.APPLCTN_TXT = JX.FULL_SYS_NM_TXT                                      
FROM TOTDB01.PROD_ABEND PA                                                     
  INNER JOIN TOTDB01.JOB_XREF JX                                               
  ON SUBSTR(PA.JOB_TXT,1,2) = JX.PFX_TXT                                       
WHERE PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130;                  
---------+---------+---------+---------+---------+---------+---------+---------
DSNT408I SQLCODE = -199, ERROR:  ILLEGAL USE OF KEYWORD FROM.  TOKEN ( .       
         MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE WAS EXPECTED   
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 2 0  0  -1  145  506 SQL DIAGNOSTIC INFORMATION          
DSNT416I SQLERRD    = X'00000002'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000091'  X'000001FA' SQL DIAGNOSTIC INFORMATION                   
---------+---------+---------+---------+---------+---------+---------+---------
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0                                      
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                      
---------+---------+---------+---------+---------+---------+---------+---------

Marcus_A · #4 (**permalink**) 08-13-10, 14:02

I assume it is something like this:

UPDATE TOTDB01.PROD_ABEND PA
SET PA.APPLCTN_TXT =
(Select X.FULL_SYS_NM_TXT From .....)

tonkuma · #5 (**permalink**) 08-13-10, 14:29

Usually, WHERE clause is required to update only rows matching with JX, like this:

UPDATE TOTDB01.PROD_ABEND PA
SET PA.APPLCTN_TXT =
(Select X.FULL_SYS_NM_TXT From .....)
WHERE EXISTS
(Select X.FULL_SYS_NM_TXT From .....)

or use MERGE statement to avoid duplicated codes in SET clause and in WHERE clause.

goldfishhh · #6 (**permalink**) 08-13-10, 15:15

This is what I came up with and it worked pretty well..

Code:

UPDATE TOTDB01.PROD_ABEND PA                          
  SET PA.APPLCTN_TXT =                                
     (SELECT JX.FULL_SYS_NM_TXT                       
      FROM TOTDB01.PROD_ABEND PABD                    
         INNER JOIN TOTDB01.JOB_XREF JX               
         ON SUBSTR(PA.JOB_TXT,1,2) = JX.PFX_TXT       
      WHERE PA.PROD_ABEND_ID = PABD.PROD_ABEND_ID     
      )                                               
WHERE PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130;

tonkuma · #7 (**permalink**) 08-13-10, 15:33

1) So, there is a row of JX which satisfy SUBSTR(PA.JOB_TXT,1,2) = JX.PFX_TXT for all rows of PA which satisfy PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130.

If there was no such matching row of JX for some rows of PA which satisfy PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130,
PA.APPLCTN_TXT would be set to null for such rows.

If there were multiple matching rows of JX for a row of PA which satisfy PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130,
you would get SQL0811N error.

2) I think that TOTDB01.PROD_ABEND PABD is not necessary.

goldfishhh · #8 (**permalink**) 08-14-10, 07:18

I Made a slight adjustment to the query and it works great.

As you can see, the first I tell it to update TS and then match TS.TST_SUC_ID = TSUC.TST_SUC_ID to make sure I don't have any dups. Then each line inspect the first 2 characters of the JOB_TXT and change the OWNR_TXT to be the right FULL_SYS_NM_TXT.

I just updated 4 tables and 19,000,000 records in just under 17mins. Pretty quick too.

Code:

   UPDATE TOTDB01.TST_SUC TS                                   
     SET TS.OWNR_TXT =                                         
        (SELECT JX.FULL_SYS_NM_TXT                             
         FROM TOTDB01.TST_SUC TSUC                             
            INNER JOIN TOTDB01.JOB_XREF JX                     
            ON SUBSTR(TSUC.JOB_TXT,1,2) = JX.PFX_TXT           
         WHERE TS.TST_SUC_ID = TSUC.TST_SUC_ID     
        );

Internet Services

Web Development

Digital Marketing

Consumer Tech