Return Number of Rows Affected by Update Statements

Return number of rows affected by UPDATE statements

CREATE PROCEDURE UpdateTables
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @RowCount1 INTEGER
    DECLARE @RowCount2 INTEGER
    DECLARE @RowCount3 INTEGER
    DECLARE @RowCount4 INTEGER

    UPDATE Table1 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount1 = @@ROWCOUNT
    UPDATE Table2 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount2 = @@ROWCOUNT
    UPDATE Table3 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount3 = @@ROWCOUNT
    UPDATE Table4 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount4 = @@ROWCOUNT

    SELECT @RowCount1 AS Table1, @RowCount2 AS Table2, @RowCount3 AS Table3, @RowCount4 AS Table4
END

TSQL: UPDATE Get num rows updated

To identify the number of rows affected by an operation, you're looking for @@ROWCOUNT

Getting the number of the rows affected by update or insert

When function/trigger is called, the new internal execution state structure is created (PLpgSQL_execstate) with ROW_COUNT value (eval_processed property) set to 0. So inside function you can get ROW_COUNT only for statements inside the same function. As a workaround you can pass sql statement as the text argument to this procedure and execute it inside.

Is a possible to check number of rows affected by update query in oracle without executing the query

You could run EXPLAIN on your update query. The output from that would include things like row counts at each step of the query pipeline. In addition, EXPLAIN would be making its estimates using table statistics, rather than hitting the actual production table.

Return number of rows affected by update query?

Try this.

int pg_affected_rows ( resource $result )

http://php.net/manual/en/function.pg-affected-rows.php

---

Related Topics