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
reread_exposure=no;

proc sql;
drop table invty.STOCK23;
quit;

/* 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.;
  datalines;
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;
quit;