KEY = Option in MODIFY Statement to update DBMS Table

[UrvishShah]

Hi,

I tried to update the DBMS Table by using following LIBNAME statement...based on my knowledge if we want to user KEY= option on DBMS Table then  reread_exposure=yes must be set...In below example, Index is already created on PK variable, so i can directly use it in KEY= option...

my_data.test contains one record and i need to update the table based on following data...

But i am getting following error...

ERROR: No key variables have been defined for file test.

ERROR: ROLLBACK issued due to errors for data set TEST.test.DATA.

LIBNAME my_data MYSQL  SERVER="xxxxx"

     DATABASE=my_database  MYSQL_PORT=3306  USER=xxxx

     PASSWORD="xxxxxxxxx" reread_exposure=yes;

data my_data;

  input pk var;

  cards;

0 6

1 7

2 8

;

data my_data.test;

  set my_data(rename=(var = new_var));

  modify my_data.test

         key = pk; /* In our case, Index is already created for SK, so we can use it */

  if _IORC_ = %sysrc(_sok) then do;

       var = new_var;

       replace;

  end;

  else if _IORC_ = %sysrc(_dsenom) then do;

       var = new_var;

       output;

       _ERROR_ = 0;

  end;

run;

Can anyone help me why i am getting this error eventhough i set the proper options...

-Urvish

[jakarman]

The modify is not often used. The message is coming from your DBMS system.

It says there have no indexes  been defined within the DBMS on your table so key= is failing.

the sastrace option (and more like that) should help in debugging. http://www2.sas.com/proceedings/sugi31/264-31.pdf