Обсуждение: Update & Lack of Error Message
i set up an update statement to update a unit's status from incomplete to completed. however, pgsql doesn't kick out an error message when i try to update something that doesn't exist. eg, serial number 21 doesn't exist in my table. when i "update" serial number 21, it goes through the motions as though it updated something, but it actually did nothing to the table data. is this expected behavior? i'm using pgsql 8.1 on winxp. i'm thinking about running a select to check the existence of the serial number before running the update. that way, i can check that the serial 1. exists and 2. hasn't already been updated and, based on the result, kick out a message to the user as appropriate. tia... tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Thu, Jan 26, 2006 at 02:22:37PM -0800, operationsengineer1@yahoo.com wrote: > i set up an update statement to update a unit's status > from incomplete to completed. > > however, pgsql doesn't kick out an error message when > i try to update something that doesn't exist. The database has no way of knowing that a certain number of rows updated (zero in your case) represents an error; that's something the application knows. An update that affects zero rows is perfectly legitimate in many applications. > i'm thinking about running a select to check the > existence of the serial number before running the > update. that way, i can check that the serial 1. > exists and 2. hasn't already been updated and, based > on the result, kick out a message to the user as > appropriate. Your API should provide a way to get the number of rows updated. What language and database interface are you using? -- Michael Fuhr
--- Michael Fuhr <mike@fuhr.org> wrote: > On Thu, Jan 26, 2006 at 02:22:37PM -0800, > operationsengineer1@yahoo.com wrote: > > i set up an update statement to update a unit's > status > > from incomplete to completed. > > > > however, pgsql doesn't kick out an error message > when > > i try to update something that doesn't exist. > > The database has no way of knowing that a certain > number of rows > updated (zero in your case) represents an error; > that's something > the application knows. An update that affects zero > rows is perfectly > legitimate in many applications. > > > i'm thinking about running a select to check the > > existence of the serial number before running the > > update. that way, i can check that the serial 1. > > exists and 2. hasn't already been updated and, > based > > on the result, kick out a message to the user as > > appropriate. > > Your API should provide a way to get the number of > rows updated. > What language and database interface are you using? > > -- > Michael Fuhr > ADOdb - and thanks for the idea. i can run the query, check for rows updated and then do whatever makes sense from that point. a zero row update can still mean two things, though - the serial doesn't exist or the serial exists, but already has been updated to the new value. i need to decide if that distinction will be important enough to differentiate the error messages. thanks again. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Thu, Jan 26, 2006 at 04:13:45PM -0800, operationsengineer1@yahoo.com wrote: > i can run the query, check for rows updated and then > do whatever makes sense from that point. > > a zero row update can still mean two things, though - > the serial doesn't exist or the serial exists, but > already has been updated to the new value. i need to > decide if that distinction will be important enough to > differentiate the error messages. If the "already updated" case is important then be aware that a row will be counted as updated even if its new and old values are the same. test=> SELECT * FROM foo; id | x ----+--- 1 | 2 (1 row) test=> UPDATE foo SET x = 2 WHERE id = 1; UPDATE 1 If you want an "already changed" update to return zero rows then add a condition to check that the column has a different value than the one you're assigning: test=> UPDATE foo SET x = 2 WHERE id = 1 AND x <> 2; UPDATE 0 You might want to use IS DISTINCT FROM instead of <> if x can be NULL. -- Michael Fuhr