Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81

    Unanswered: embedded sql and rowcount

    Hi,

    I am running in embedded something like

    UPDATE mytable SET field=value WHERE key=keyvalue


    How can I know whether 0 or 1 row(s) were affected ?

    Tx.
    --
    kukuk

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I'm not sure what you mean by running in embedded, but this should do the trick in PL/SQL

    UPDATE mytable SET field=value WHERE key=keyvalue;
    IF SQL%ROWCOUNT = 0 THEN
    .....

    HTH
    Bill

  3. #3
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    Found it:

    [SQLCA description]
    ...

    sqlerrd[2]
    This component holds the number of rows processed by the most recently executed SQL statement. However, if the SQL statement failed, the value of sqlca.sqlerrd[2] is undefined, with one exception. If the error occurred during an array operation, processing stops at the row that caused the error, so sqlca.sqlerrd[2] gives the number of rows processed successfully.


    The rows-processed count is zeroed after an OPEN statement and incremented after a FETCH statement. For the EXECUTE, INSERT, UPDATE, DELETE, and SELECT INTO statements, the count reflects the number of rows processed successfully. The count does not include rows processed by an UPDATE or DELETE CASCADE. For example, if 20 rows are deleted because they meet WHERE-clause criteria, and 5 more rows are deleted because they now (after the primary delete) violate column constraints, the count is 20 not 25.
    --
    kukuk

  4. #4
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Run this query and you will see how many rows will be affected by the UPDATE statement:

    SELECT count(*) FROM mytable WHERE key=keyvalue;



    Hope that helps,

    clio_usa - OCP - DBA

    dbaclick.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •