Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

All records are being updated instead of updating a single record using
SAS/ACCESS Interface to MySQL with PROC SQL UPDATE, if multiple records
exist.

Below is an example demonstrating the problem.

 LIBNAME mysqllib MYSQL MYSQL_PORT=3306 SERVER=localhost
    DATABASE=test;

  proc sql ;
    /* Close out existing records  */
    update mysqllib."SCDBEGENDDATEJOB3OUT"n
      set "END_DATE"n = (
         select "ETLS_CLSDATE"n
         from work.etls_close
         where "SSN"n
             = "SCDBEGENDDATEJOB3OUT"n."SSN"n and "ETLS_FROMDATE"n
             = "SCDBEGENDDATEJOB3OUT"n."BEG_DATE"n )
      where "SSN"n = (
         select "SSN"n
          from work.etls_close
          where "SSN"n = "SCDBEGENDDATEJOB3OUT"n."SSN"n
          and "ETLS_FROMDATE"n =
            "SCDBEGENDDATEJOB3OUT"n."BEG_DATE"n )
            and "BEG_DATE"n = (
               select "ETLS_FROMDATE"n
               from work.etls_close
               where "SSN"n = "SCDBEGENDDATEJOB3OUT"n."SSN"n
               and "ETLS_FROMDATE"n =
               "SCDBEGENDDATEJOB3OUT"n."BEG_DATE"n );
       Quit;

NOTICE: records 12 thru 14 all have the same END_DATE.

      SSN     FIRST_NAME  LAST_NAME    BEG_DATE           END_DATE
12 748657687   Patrick      Hard   26AUG2004:16:35:11 26AUG2004:16:38:18
13 748657687   Patrick      Hard   26AUG2004:16:37:03 26AUG2004:16:38:18
14 748657687   Patrick      Hard   26AUG2004:16:37:11 26AUG2004:16:38:18
15 748657687   Patrick      Hard   26AUG2004:16:38:19 01JAN5999:00:00:00

The RESULTS should be as follows where only number 14 is updated.

      SSN     FIRST_NAME  LAST_NAME    BEG_DATE           END_DATE
12 748657687   Patrick      Hard   26AUG2004:16:35:11 26AUG2004:16:37:02
13 748657687   Patrick      Hard   26AUG2004:16:37:03 26AUG2004:16:37:10
14 748657687   Patrick      Hard   26AUG2004:16:37:11 26AUG2004:16:38:18
15 748657687   Patrick      Hard   26AUG2004:16:38:19 01JAN5999:00:00:00

Deleting with multiple keys seems to also cause this problem.



A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

http://www.sas.com/techsup/download/hotfix/e9_sbcs_prod_list.html#013322

For customers running SAS with Asian Language Support (DBCS), this
fix should be downloaded from:

http://www.sas.com/techsup/download/hotfix/e9_dbcs_prod_list.html#013322

Operating System and Release Information

A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at :