SAS/ACCESS Interface for MySQL |
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;
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.