Ok, I have an update function with a weird twist. Due to the nature of the structure, I run a delete query then insert query, rather than an actual “Update” query. They are specifically run in that order so that the new items inserted are not deleted. Essentially, items are deleted by an attribute id that matches in the insert query. Since the attribute is not a primary index, “ON DUPLICATE KEY UPDATE” is not working.
So here’s the dilemma. During development and testing, The delete query will run without fail, but if I’m screwing around with the input for the INSERT query and it fails, then the DATA has been deleted without being reinserted, which means regenerating new test data, and even worse, if it fails in production, then the user will lose everything they were working on.
So, I know MySQL validates a query before it is actually run, so is it possible to make sure the INSERT query validates before running the DELETE query?
<cfquery name="delete" datasource="DSOURCE">
DELETE FROM table
WHERE colorid = 12
<!--- check this query first before running delete --->
<cfquery name="insert" datasource="DSOURCE">
INSERT INTO table (Name, ColorID)
VALUES ("tom", 12)