CODEWITHSUNDEEP
mysqlcoldfusion
Kyle Macey
Kyle Macey

Reputation: 8154

Verify a query is going to work before executing another query in reverse order

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
</cfquery>

<!--- check this query first before running delete --->
<cfquery name="insert" datasource="DSOURCE">
  INSERT INTO table (Name, ColorID)
  VALUES ("tom", 12)
</cfquery>

Upvotes: 2

Views: 233

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65304

Since you use WHERE colorid = 12 as your delete criterium, colorid must be a unique key. This gives you two ways of approachng this with a single query

UPDTAE table SET NAME="tom"
  WHERE colorid=12

OR

REPLACE INTO table (Name, ColorID)
  VALUES ("tom", 12)

Upvotes: 0

N.B.
N.B.

Reputation: 14091

You have 2 problems.

Since the attribute is not a primary index, "ON DUPLICATE KEY UPDATE" is not working.

Attribute doesn't have to be PRIMARY KEY. It's sufficient if it's defined as UNIQUE KEY, which you can do without penalties.

And number two: if you want to execute a series of queries in sequence, with ALL of them being successful and none failing - the term is transaction. Either all succeed or nothing happens. Google about MySQL transactions to get better overview of how to use them.

Upvotes: 3

Related Questions