Make sure only one row is updated in PL/SQL?

Question

I have a long list of UPDATE statements that I run on my Oracle 11g server:

UPDATE Table SET Column1 = 'One' WHERE Column2 = 'Example';
UPDATE Table SET Column1 = 'Two' WHERE Column2 = 'Something';
-- ...

I want to check that each UPDATE affects exactly one row (not zero, not more than one), and if it doesn't stop the execution, raise an error, and roll back all of the updates.

I was hoping for a syntax like this perhaps:

UPDATE Table SET Column1 = 'One' WHERE Column2 = 'Example';
ASSERT sql%rowcount = 1;

That does not seem to exist, so maybe I should do something like this:

UPDATE Table SET Column1 = 'One' WHERE Column2 = 'Example';
IF sql%rowcount != 1 THEN
  -- Do something, but what?
END IF;

However, I am not sure what to put inside the IF statement or if that is even a good approach. So, what is the most elegant way of accomplishing this?

[here](http://stackoverflow.com/questions/861983/number-of-rows-affected-by-an-update-in-pl-sql) similar situation, i think it will help you — The Reason, Aug 19 '15 at 08:55
If you have a unique key on "Key" then you only have to test whether any rows were updated at all. — David Aldridge, Aug 19 '15 at 09:04
@DavidAldridge Sorry, the choise of column name might have been a bit misleading. There are different things in the `WHERE` clauses and I can not be sure there is not more than one match. — Anders, Aug 19 '15 at 09:10

MT0 · Accepted Answer · 2015-08-19T09:27:19.730

UPDATE your_table SET Column = 'One' WHERE Key = 'Example';
IF SQL%ROWCOUNT <> 1 THEN
  ROLLBACK;
  RAISE_APPLICATION_ERROR( -20000, 'Incorrect number of rows updated for Key "Example".' )
END IF;

Or:

DECLARE
  p_rowid ROWID;
  p_key   YOUR_TABLE.KEY%TYPE := 'Example';
BEGIN
  SELECT ROWID INTO p_rowid FROM your_table WHERE Key = p_key;

  UPDATE your_table SET Column = 'One' WHERE ROWID = p_rowid;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR( -20000, 'No data found for key "'|| p_key || '".' );
  WHEN TOO_MANY_ROWS THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR( -20001, 'More than one row found for key "'|| p_key || '".' );
END;

score 3 · Answer 2 · answered Aug 19 '15 at 08:59

3

As you expect the updates to affect one row each, you could simply put the query to get that one row in a subquery, which would result in an ORA-01427 exception ("single-row subquery returns more than one row") when there is more than one.

UPDATE Table SET Column = 'One' WHERE ROWID = (SELECT ROWID FROM TABLE WHERE Key = 'ABC');
UPDATE Table SET Column = 'Two' WHERE ROWID = (SELECT ROWID FROM TABLE WHERE Key = 'DEF');
...

answered Aug 19 '15 at 08:59

Thorsten Kettner

89,309
7
49
73

Thank you for the answer. Would this have any performance implications? Would it also ensure that all previous updates are rolled back if one fails? – Anders Aug 19 '15 at 09:00
1

Performance issues, if any, should be small, as this is about what the DBMS does internally anyhow: first find the row, then update. And yes, you are in one transaction. As long as you haven't issued a COMMIT, an exception would rollback *all* updates. – Thorsten Kettner Aug 19 '15 at 09:03
@jva: Darn it, you are right! So my answer doesn't solve the request and we are back to checking SQL%ROWCOUNT and raise an exception ourselves. Sorry Anders. – Thorsten Kettner Aug 19 '15 at 09:06

score 3 · Answer 3 · answered Aug 19 '15 at 09:02

Easy way for one statement:

SAVEPOINT before_my_update;

UPDATE Table 
SET Column = 'One' 
WHERE Key = 'Example';

IF sql%rowcount != 1 THEN
  ROLLBACK TO SAVEPOINT before_my_update;
  raise_application_error(-20000,'More than one row affected!');
END IF;

For all updates on table:

create package with global variable
in statement level before update trigger initialize it to 0
in row level before update trigger check if it is 0.
- if it is not 0, raise error
- if it is 0, increment it by 1

Make sure only one row is updated in PL/SQL?

3 Answers3