Get list of rows affected by UPDATE?

SQL, Caché

I need to do an UPDATE via SQL and I would like the statement to return the `ID` column of each row that is updated.  MS SQL has an "OUTPUT" statement, but I don't see anything similar in Cache.  Is there a way to do this?

  • 0
  • 0
  • 341
  • 3
  • 1

Answers

Yes. You can use cursors for that. In the following example rowlist contains list of affected ids. You can get it all at the end or get individual ids right before or after the update, or even decide on the update based on id/val values:

Class User.NewClass1 Extends %Persistent
{

Property val;

/// do ##class(User.NewClass1).Test()
ClassMethod Test()
{
   do ..%KillExtent()
   
   &sql(INSERT INTO NewClass1 SET val = 0)
   &sql(INSERT INTO NewClass1 SET val = 3)
   
   set rowlist = ""
   &sql(DECLARE NewClass1 CURSOR FOR
        SELECT %ID,val
        INTO :id, :val
        FROM NewClass1)
   
   &sql(OPEN NewClass1)
   for {
       &sql(FETCH NewClass1)
       quit:SQLCODE'=0
       set val2 = val*2
       write "Processing id: ", id,!
       set rowlist = rowlist _ $lb(id)
       &sql(UPDATE NewClass1 SET val = :val2 WHERE CURRENT OF NewClass1)
   }
   &sql(CLOSE NewClass1)
   
   zw rowlist
}
}

It would output in a terminal:

>do ##class(User.NewClass1).Test()
Processing id: 1
Processing id: 2
rowlist=$lb("1","2")

Documentation: