Oracle PLS-00103 error. How do you check for an existing record and do update or insert based on that condition?
We should always use SQL whenever possible, and avoid using Pl/SQL unless it is strictly necessary. SQL statements perform faster, they usually require less typing and they are easier to get right.
Since 9i Oracle has provided MERGE, a single SQL statement which executes an "upsert" statement.
MERGE INTO frec_email tUSING (SELECT 'blah@foo.com' as email , '208254846' as FranchiseNo , 1 as ReportID , 165 as ID FROM dual ) sON (s.ID = t.ID)WHEN MATCHED THEN UPDATE SET t.email = s.emailWHEN NOT MATCHED THEN INSERT (t.FranchiseNo, t.Email, t.ReportID) VALUES (s.FranchiseNo, s.Email, s.ReportID)/