Understanding MySQL Update and Delete Rules

To avoid data integrity problems when updating or deleting data, you need to specify a primary key on your table. See MySQL documentation for more information about table types and transactions.

This example uses AUTOCOMMIT=NO and DBTYPE= to create the primary key and also DBCREATE_TABLE_OPTS= to determine the MySQL table type.

libname invty mysql user=myusr1 server=mysrv1 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="engine = innodb");
  input PARTNO $ DESCX $ INSTOCK @20
        RECDATE date7. @29 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
;

This next example shows 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;