Understanding MySQL Update and Delete Rules

To avoid data integrity problems when updating or deleting data, you need a primary key defined on your table. See the MySQL documentation for more information about table types and transactions.
The following example uses AUTOCOMMIT=NO and DBTYPE to create the primary key, and DBCREATE_TABLE_OPTS to determine the MySQL table type.
libname invty mysql user=dbitest server=d6687 database=test autocommit=no

proc sql;
drop table invty.STOCK23;

/* Create DBMS table with primary key and of type INNODB*/
data invty.STOCK23(drop=PARTNO DBTYPE=(RECDATE="date not null,
     primary key(RECDATE)") DBCREATE_TABLE_OPTS="type = innodb");
  input PARTNO $ DESCX $ INSTOCK @17
        RECDATE date7. @25 PRICE;
  format RECDATE date7.;
K89R seal  34   27jul95 245.00
M447 sander 98  20jun95 45.88
LK43 filter 121 19may96 10.99
MN21 brace 43   10aug96 27.87
BC85 clamp 80   16aug96 9.55
KJ66 cutter 6   20mar96 24.50
UYN7 rod  211   18jun96 19.77
JD03 switch 383 09jan97 13.99
BV1I timer 26   03jan97 34.50
The next examples show how you can update the table now that STOCK23 has a primary key:
proc sql;
update invty.STOCK23 set price=price*1.1 where INSTOCK > 50;