Determining the number of rows affected by DML commands¶

After a DML command is executed (excluding the TRUNCATE TABLE command), Snowflake Scripting sets the following global variables. You can use these variables to determine if the last DML statement affected any rows.

The examples in this section use the following table:

CREATE OR REPLACE TABLE my_values (value NUMBER);

The following example uses the SQLROWCOUNT variable to return the number of rows affected by the last DML statement (the INSERT statement). The SELECT statement isn’t a DML statement and has no effect on the SQLROWCOUNT variable.

BEGIN
  INSERT INTO my_values VALUES (1), (2), (3);
  SELECT * from my_values;
  RETURN SQLROWCOUNT;
END;

Note: If you are using SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE $$
BEGIN
  INSERT INTO my_values VALUES (1), (2), (3);
  SELECT * from my_values;
  RETURN SQLROWCOUNT;
END;
$$;

+-----------------+
| anonymous block |
|-----------------|
|               3 |
+-----------------+

The following example uses the SQLFOUND and SQLNOTFOUND variables to return the number of rows affected by the last DML statement (the UPDATE statement). As is the case with the previous example, the SELECT statement doesn’t affect the SQLFOUND and SQLNOTFOUND variables.

BEGIN
  IF ((SELECT MAX(value) FROM my_values) > 2) THEN
    UPDATE my_values SET value = 4 WHERE value < 3;
  END IF;
  SELECT * from my_values;
  IF (SQLFOUND = true) THEN
    RETURN 'Updated ' || SQLROWCOUNT || ' rows.';
  ELSEIF (SQLNOTFOUND = true) THEN
    RETURN 'No rows updated.';
  ELSE
    RETURN 'No DML statements executed.';
  END IF;
END;

Note: If you are using SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE $$
BEGIN
  IF ((SELECT MAX(value) FROM my_values) > 2) THEN
    UPDATE my_values SET value = 4 WHERE value < 3;
  END IF;
  SELECT * from my_values;
  IF (SQLFOUND = true) THEN
    RETURN 'Updated ' || SQLROWCOUNT || ' rows.';
  ELSEIF (SQLNOTFOUND = true) THEN
    RETURN 'No rows updated.';
  ELSE
    RETURN 'No DML statements executed.';
  END IF;
END;
$$;

When the anonymous block runs, the SQLFOUND variable is true because the UPDATE statement updates two rows.

+-----------------+
| anonymous block |
|-----------------|
| Updated 2 rows. |
+-----------------+

Query the table to see the current values:

SELECT * FROM my_values;

+-------+
| VALUE |
|-------|
|     4 |
|     4 |
|     3 |
+-------+

Run the same anonymous block again, and the results are the following:

• The UPDATE statement is executed because there is a value in the table that is greater than 2. That is, the IF condition is satisfied.

• The SQLNOTFOUND variable is true because no rows are updated. The UPDATE statement doesn’t update any rows because none of the values in the table are less than 3 (specified in the WHERE clause).

The query returns the following output:

+------------------+
| anonymous block  |
|------------------|
| No rows updated. |
+------------------+

Now, update the table to set all of the values to 1:

UPDATE my_values SET value = 1;

SELECT * FROM my_values;

+-------+
| VALUE |
|-------|
|     1 |
|     1 |
|     1 |
+-------+

Run the same anonymous block again, and the UPDATE statement isn’t executed because none of the values in the table are greater than 2. That is, the IF condition isn’t satisfied, so the UPDATE statement doesn’t execute.

+-----------------------------+
| anonymous block             |
|-----------------------------|
| No DML statements executed. |
+-----------------------------+