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;