第八十五章 SQL命令 UPDATE（四）

示例

本节中的示例更新SQLUser.MyStudents表。
下面的示例创建SQLUser.MyStudents表，并用数据填充它。
因为这个示例的重复执行会积累具有重复数据的记录，所以它使用TRUNCATE TABLE在调用INSERT之前删除旧数据。
在调用UPDATE示例之前执行这个示例:

ClassMethod Update1()
{
CreateStudentTable
    s stuDDL = 5
    s stuDDL(1) = "CREATE TABLE SQLUser.MyStudents ("
    s stuDDL(2) = "StudentName VARCHAR(32),StudentDOB DATE,"
    s stuDDL(3) = "StudentAge INTEGER COMPUTECODE {SET {StudentAge}="
    s stuDDL(4) = "$PIECE(($PIECE($H,"","",1)-{StudentDOB})/365,""."",1)} CALCULATED,"
    s stuDDL(5) = "Q1Grade CHAR,Q2Grade CHAR,Q3Grade CHAR,FinalGrade VARCHAR(2))"
    s tStatement = ##class(%SQL.Statement).%New(0,"Sample")
    s qStatus = tStatement.%Prepare(.stuDDL)
    if qStatus'=1 {
        w "DDL %Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rtn = tStatement.%Execute()
    if rtn.%SQLCODE = 0 {
        w !,"表创建成功"
    } elseif rtn.%SQLCODE = -201 {
        w "表已经存在, SQLCODE=",rtn.%SQLCODE,!
    } else {
        w !,"表创建失败, SQLCODE=",rtn.%SQLCODE,!
        w rtn.%Message,! 
    }
RemoveOldData
    s clearit = "TRUNCATE TABLE SQLUser.MyStudents"
    s qStatus = tStatement.%Prepare(clearit)
    if qStatus'=1 {
        w "Truncate %Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s truncrtn = tStatement.%Execute()
    if truncrtn.%SQLCODE = 0 {
        w !,"表旧数据已经被删除",!
    } elseif truncrtn.%SQLCODE = 100 {
        w !,"没有数据可以删除",!
    } else {
        w !,"truncate failed, SQLCODE=",truncrtn.%SQLCODE," ",truncrtn.%Message,! 
    }
PopulateStudentTable
    s studentpop=2
    s studentpop(1)="INSERT INTO SQLUser.MyStudents (StudentName,StudentDOB) "
    s studentpop(2)="SELECT Name,DOB FROM Sample.Person WHERE Age <= '21'"
    s qStatus = tStatement.%Prepare(.studentpop)
    if qStatus '= 1 {
        w "填充 %Prepare 失败:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s poprtn = tStatement.%Execute()
    if poprtn.%SQLCODE = 0 {
        w !,"表填充成功",!
        w poprtn.%ROWCOUNT," rows inserted"
    } else {
        w !,"表填充失败, SQLCODE=",poprtn.%SQLCODE,!
        w poprtn.%Message 
    }
}

可以使用下面的查询来显示这些示例的结果:

SELECT %ID,* FROM SQLUser.MyStudents ORDER BY StudentAge,%ID

下面的一些UPDATE示例依赖于其他UPDATE示例设置的字段值;
它们应该按照指定的顺序运行。

在下面的动态SQL示例中，SET field=value UPDATE修改选定记录中的指定字段。
在我的学生表中，7岁以下的孩子是不给分数的:

ClassMethod Update2()
{
    s studentupdate=3
    s studentupdate(1)="UPDATE SQLUser.MyStudents "
    s studentupdate(2)="SET FinalGrade='NA' "
    s studentupdate(3)="WHERE StudentAge <= 6"
    s tStatement = ##class(%SQL.Statement).%New(0,"Sample")
    s qStatus = tStatement.%Prepare(.studentupdate)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s uprtn = tStatement.%Execute()
    if uprtn.%SQLCODE=0 {
        w !,"表更新成功"
        w !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID
    } else {
        w !,"Table update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message 
    }
}

在下面的基于游标的嵌入式SQL示例中，SET field1=value1,field2=value2 UPDATE修改选定记录中的几个字段。
在MyStudents表中，它用Q1和Q2成绩更新指定的学生记录:

ClassMethod Update2()
{
    #SQLCompile Path = Sample
    n %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)
    q:(SQLCODE'=0)
    for { 
        &sql(FETCH StuCursor)
        q:SQLCODE 
        &sql(
            Update MyStudents SET Q1Grade='A',Q2Grade='A'
            WHERE CURRENT OF StuCursor
        )
        if SQLCODE=0 {
            w !,"表更新成功"
            w !,"Row count=",%ROWCOUNT," RowID=",%ROWID 
        } else {
            w !,"表更新失败, SQLCODE=",SQLCODE 
        }
    }
    &sql(CLOSE StuCursor)
}

在下面的动态SQL示例中，字段列表VALUES值列表UPDATE修改选定记录中几个字段的值。
在我的学生表中，没有拿到期末成绩的孩子也没有拿到季度成绩:

ClassMethod Update4()
{
    s studentupdate=3
    s studentupdate(1)="UPDATE SQLUser.MyStudents "
    s studentupdate(2)="(Q1Grade,Q2Grade,Q3Grade) VALUES ('x','x','x') "
    s studentupdate(3)="WHERE FinalGrade='NA'"
    s tStatement = ##class(%SQL.Statement).%New()
    s qStatus = tStatement.%Prepare(.studentupdate)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s uprtn = tStatement.%Execute()
    if uprtn.%SQLCODE=0 {
        w !,"Table Update successful"
        w !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID
    } else {
        w !,"Table Update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message,! 
    }
}

在下面的动态SQL示例中，VALUES值列表UPDATE修改所选记录中的所有字段值。
注意，此语法要求为记录中的每个字段指定一个值。
在“user.student”表中，有几个孩子已经从学校退学。
保留它们的记录id和名称，并在名称后附加单词withdraw;
所有其他数据被删除，DOB字段用于提取日期:

ClassMethod Update5()
{
    s studentupdate=4
    s studentupdate(1)="UPDATE SQLUser.MyStudents "
    s studentupdate(2)="VALUES (StudentName||' WITHDRAWN',"
    s studentupdate(3)="$PIECE($HOROLOG,',',1),00,'-','-','-','XX') "
    s studentupdate(4)="WHERE %ID IN(7,10,22)"
    s tStatement = ##class(%SQL.Statement).%New()
    s qStatus = tStatement.%Prepare(.studentupdate)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s uprtn = tStatement.%Execute()
    if uprtn.%SQLCODE=0 {
        w !,"Table Update successful"
        w !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID
    } else {
        w !,"Table Update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message,! 
    }
}

在下面的动态SQL示例中，子查询UPDATE使用子查询来选择记录。
然后使用SET field=value语法修改这些记录。
因为在SQLUser中，StudentAge是从出生日期开始计算的。
我的学生们，任何不满一年的人的计算年龄都是<Null>，任何出生日期已被取消的人的计算年龄都非常高。
这里标记了StudentName字段，以便将来确认出生日期:

ClassMethod Update6()
{
    s studentupdate=3
    s studentupdate(1)="UPDATE (SELECT StudentName FROM SQLUser.MyStudents "
    s studentupdate(2)="WHERE StudentAge IS NULL OR StudentAge > 21) "
    s studentupdate(3)="SET StudentName = StudentName||' *** CHECK DOB' "
    s tStatement = ##class(%SQL.Statement).%New(0,"Sample")
    s qStatus = tStatement.%Prepare(.studentupdate)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s uprtn = tStatement.%Execute()
    if uprtn.%SQLCODE=0 {
        w !,"Table Update successful"
        w !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID
    } else {
        w !,"Table Update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message,! 
    }
}

在下面的嵌入式SQL示例中，VALUES:array() UPDATE修改选定记录中的数组中列号指定的字段值。
只能在嵌入式SQL中进行VALUES:array()更新。
注意，此语法要求您通过DDL列号指定每个值(包括在列计数中RowID列(列1)，但不为这个不可修改的字段提供值)。
在MyStudents表中，在Q1Grade(第5列)和Q2Grade(第6列)字段中，4到6岁(包括4到6岁)的孩子会得到一个“P”(代表“Present”)。
所有其他记录数据保持不变:

ClassMethod Update7()
{
    s arry(5)="P"
    s arry(6)="P"
    &sql(
        update SQLUser.MyStudents VALUES :arry() 
        where FinalGrade='NA' AND StudentAge > 3)
    if SQLCODE=0 {
        w "Table Update successful",!
        w "Rows updated=",%ROWCOUNT," Final RowID=",%ROWID
    } else {
        w "Table Update failed, SQLCODE=",SQLCODE,! 
    }
}