Re^3: Perl DBI execute statement

I am not a DB guru, but a failed INSERT sounds like a pretty big deal to me. Example: (primary_key,b,c,d). If an insert of that record fails because the primary_key exists, what about the values of b,c,d? Maybe this "INSERT" had new values for b,c,d?

I would think that instead of an INSERT, an UPDATE might be more appropriate in your situation? If the UPDATE fails because no record with that primary key exists, then you INSERT a brand new record with that primary key and data fields, otherwise if the UPDATE succeeds, the data fields other than primary are updated.

Comment on Re^3: Perl DBI execute statement

Replies are listed 'Best First'.
Re^4: Perl DBI execute statement by cbtshare (Monk) on Dec 12, 2016 at 19:07 UTC
correct it has the same id_code which is the primary key, but new data, I am not sure about the clause to update if primary exist and insert if it doesn't.	[reply]
Re^5: Perl DBI execute statement by Marshall (Canon) on Dec 14, 2016 at 19:49 UTC
The execute will return a value indicated the number of rows affected. In the case of an UPDATE, it will be the number of rows modified. If no rows are modified, it will return "0E0" which means that the statement "succeeded" (a "true" logical value), but no rows were modified (a numeric zero). If say something is "not right" in the WHERE clause of the update, a 0E0 return value would happen. This is not considered an "error". Method 1: You could try to UPDATE the record and then if that doesn't update the record ("0E0"), then INSERT that record, of course checking that the SQL INSERT succeeded with a "1" value (one row inserted). This is two SQL statements, although just 1 if the record already exists (UPDATE updates the existing row). Method 2: You could also do a simple SELECT on the primary key and see if it is there or not? (just check number of rows returned). If the primary key exists, then UPDATE, else INSERT. I suspect that this is better for your debugging... print the results of the select, update and insert execute values in your debugging code. This is 2 actually executed SQL statements. I think Method 2 will work well for you. Set things up so that the INSERT will never fail due to a "duplicate primary key" error. Yes, it is possible to intercept the "die" from a failed INSERT and do something else, but I think that is ~~too complicated to mess with~~ not appropriate in this situation. Your final code should not have any IGNORE's in the SQL. You have to set things up so that this either never happens (unless real catastrophic error) or you explicitly deal with this exception. IGNORE shouldn't be one of the options. It is a very bad idea to IGNORE something that would normally "crash" the program.	[reply]