Cleanup a table

In the example, a large table named target is created and populated. A character column, dactyl, inadvertently acquires some null values. These rows should be deleted. Furthermore, a new column, serials, is added to the table with the ALTER TABLE statement. This column is to have unique integer values installed. The following example shows the code you use to accomplish these tasks:

EXEC SQL BEGIN DECLARE SECTION;
char dcol[80];
short dcolint;
int sequence;
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE target_row CURSOR FOR
   SELECT datcol
      INTO :dcol:dcolint
      FROM target
   FOR UPDATE OF serials;
EXEC SQL BEGIN WORK;
EXEC SQL OPEN target_row;
if (sqlca.sqlcode == 0) EXEC SQL FETCH NEXT target_row;
for(sequence = 1; sqlca.sqlcode == 0; ++sequence)
{
   if (dcolint < 0) /* null datcol */
      EXEC SQL DELETE WHERE CURRENT OF target_row;
   else
      EXEC SQL UPDATE target SET serials = :sequence
         WHERE CURRENT OF target_row;
}
if (sqlca.sqlcode >= 0)
   EXEC SQL COMMIT WORK;
else EXEC SQL ROLLBACK WORK;