Examples
The examples in this section update the SQLUser.MyStudents table. The following example creates the SQLUser.MyStudents table and populates it with data. Because repeated execution of this example would accumulate records with duplicate data, it uses TRUNCATE TABLE to remove old data before invoking INSERT. Execute this example before invoking the UPDATE examples:
CreateStudentTable
SET stuDDL=5
SET stuDDL(1)="CREATE TABLE SQLUser.MyStudents ("
SET stuDDL(2)="StudentName VARCHAR(32),StudentDOB DATE,"
SET stuDDL(3)="StudentAge INTEGER COMPUTECODE {SET {StudentAge}="
SET stuDDL(4)="$PIECE(($PIECE($H,"","",1)-{StudentDOB})/365,""."",1)} CALCULATED,"
SET stuDDL(5)="Q1Grade CHAR,Q2Grade CHAR,Q3Grade CHAR,FinalGrade VARCHAR(2))"
SET tStatement = ##class(%SQL.Statement).%New(0,"Sample")
SET qStatus = tStatement.%Prepare(.stuDDL)
IF qStatus'=1 {WRITE "DDL %Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rtn = tStatement.%Execute()
IF rtn.%SQLCODE=0 {WRITE !,"Table Create successful"}
ELSEIF rtn.%SQLCODE=-201 {WRITE "Table already exists, SQLCODE=",rtn.%SQLCODE,!}
ELSE {WRITE !,"table create failed, SQLCODE=",rtn.%SQLCODE,!
WRITE rtn.%Message,! }
RemoveOldData
SET clearit="TRUNCATE TABLE SQLUser.MyStudents"
SET qStatus = tStatement.%Prepare(clearit)
IF qStatus'=1 {WRITE "Truncate %Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET truncrtn = tStatement.%Execute()
IF truncrtn.%SQLCODE=0 {WRITE !,"Table old data removed",!}
ELSEIF truncrtn.%SQLCODE=100 {WRITE !,"no data to be removed",!}
ELSE {WRITE !,"truncate failed, SQLCODE=",truncrtn.%SQLCODE," ",truncrtn.%Message,! }
PopulateStudentTable
SET studentpop=2
SET studentpop(1)="INSERT INTO SQLUser.MyStudents (StudentName,StudentDOB) "
SET studentpop(2)="SELECT Name,DOB FROM Sample.Person WHERE Age <= '21'"
SET qStatus = tStatement.%Prepare(.studentpop)
IF qStatus'=1 {WRITE "Populate %Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET poprtn = tStatement.%Execute()
IF poprtn.%SQLCODE=0 {WRITE !,"Table Populate successful",!
WRITE poprtn.%ROWCOUNT," rows inserted"}
ELSE {WRITE !,"table populate failed, SQLCODE=",poprtn.%SQLCODE,!
WRITE poprtn.%Message }
You can use the following query to display the results of these examples:
SELECT %ID,* FROM SQLUser.MyStudents ORDER BY StudentAge,%ID
Some of the following UPDATE examples depend on field values set by other UPDATE examples; they should be run in the order specified.
In the following Dynamic SQL example, a SET field=value UPDATE modifies a specified field in selected records. In the MyStudents table, children under the age of 7 are not given grades:
SET studentupdate=3
SET studentupdate(1)="UPDATE SQLUser.MyStudents "
SET studentupdate(2)="SET FinalGrade='NA' "
SET studentupdate(3)="WHERE StudentAge <= 6"
SET tStatement = ##class(%SQL.Statement).%New(0,"Sample")
SET qStatus = tStatement.%Prepare(.studentupdate)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET uprtn = tStatement.%Execute()
IF uprtn.%SQLCODE=0 {WRITE !,"Table Update successful"
WRITE !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID}
ELSE {WRITE !,"Table update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message }
In the following cursor-based Embedded SQL example, a SET field1=value1,field2=value2 UPDATE modifies several fields in selected records. In the MyStudents table, it updates specified student records with Q1 and Q2 grades:
#sqlcompile path=Sample
NEW %ROWCOUNT,%ROWID
&sql(DECLARE StuCursor CURSOR FOR
SELECT * FROM MyStudents
WHERE %ID IN(10,12,14,16,18,20,22,24) AND StudentAge > 6)
&sql(OPEN StuCursor)
QUIT:(SQLCODE'=0)
FOR { &sql(FETCH StuCursor)
QUIT:SQLCODE
&sql(Update MyStudents SET Q1Grade='A',Q2Grade='A'
WHERE CURRENT OF StuCursor)
IF SQLCODE=0 {
WRITE !,"Table Update successful"
WRITE !,"Row count=",%ROWCOUNT," RowID=",%ROWID }
ELSE {
WRITE !,"Table Update failed, SQLCODE=",SQLCODE }
}
&sql(CLOSE StuCursor)
In the following Dynamic SQL example, a field-list VALUES value-list UPDATE modifies the values of several fields in selected records. In the MyStudents table, children who don’t receive a final grade also don’t receive quarterly grades:
SET studentupdate=3
SET studentupdate(1)="UPDATE SQLUser.MyStudents "
SET studentupdate(2)="(Q1Grade,Q2Grade,Q3Grade) VALUES ('x','x','x') "
SET studentupdate(3)="WHERE FinalGrade='NA'"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(.studentupdate)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET uprtn = tStatement.%Execute()
IF uprtn.%SQLCODE=0 {WRITE !,"Table Update successful"
WRITE !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID}
ELSE {WRITE !,"Table Update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message,! }
In the following Dynamic SQL example, a VALUES value-list UPDATE modifies all the field values in selected records. Note that this syntax requires that you specify a value for every field in the record. In the MyStudents table, several children have been withdrawn from school. Their record IDs and names are retained, with the word WITHDRAWN appended to the name; all other data is removed and the DOB field is used for the withdrawal date:
SET studentupdate=4
SET studentupdate(1)="UPDATE SQLUser.MyStudents "
SET studentupdate(2)="VALUES (StudentName||' WITHDRAWN',"
SET studentupdate(3)="$PIECE($HOROLOG,',',1),00,'-','-','-','XX') "
SET studentupdate(4)="WHERE %ID IN(7,10,22)"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(.studentupdate)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET uprtn = tStatement.%Execute()
IF uprtn.%SQLCODE=0 {WRITE !,"Table Update successful"
WRITE !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID}
ELSE {WRITE !,"Table Update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message,! }
In the following Dynamic SQL example, a subquery UPDATE uses a subquery to select records. It then modifies these records using SET field=value syntax. Because of the way that StudentAge is calculated from date of birth in SQLUser.MyStudents, anyone less than a year old has a calculated age of <Null>, and anyone whose date of birth has been nulled has a very high calculated age. Here the StudentName field is flagged for future confirmation of the date of birth:
SET studentupdate=3
SET studentupdate(1)="UPDATE (SELECT StudentName FROM SQLUser.MyStudents "
SET studentupdate(2)="WHERE StudentAge IS NULL OR StudentAge > 21) "
SET studentupdate(3)="SET StudentName = StudentName||' *** CHECK DOB' "
SET tStatement = ##class(%SQL.Statement).%New(0,"Sample")
SET qStatus = tStatement.%Prepare(.studentupdate)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET uprtn = tStatement.%Execute()
IF uprtn.%SQLCODE=0 {WRITE !,"Table Update successful"
WRITE !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID}
ELSE {WRITE !,"Table Update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message,! }
In the following Embedded SQL example, a VALUES :array() UPDATE modifies the field values specified by column number in the array in selected records. A VALUES :array() update can only be done in Embedded SQL. Note that this syntax requires that you specify each value by DDL column number (including in your column count the RowID column (column 1) but supplying no value to this non-modifiable field). In the MyStudents table, children between 4 and 6 (inclusive) are given a ‘P’ (for ‘Present’) in their Q1Grade (column 5) and Q2Grade (column 6) fields. All other record data remains unchanged:
SET arry(5)="P"
SET arry(6)="P"
&sql(UPDATE SQLUser.MyStudents VALUES :arry()
WHERE FinalGrade='NA' AND StudentAge > 3)
IF SQLCODE=0 {WRITE "Table Update successful",!
WRITE "Rows updated=",%ROWCOUNT," Final RowID=",%ROWID }
ELSE {WRITE "Table Update failed, SQLCODE=",SQLCODE,! }
