I have a bit of a strange question. I know about insert on duplicate key update. My question is, is there something similar for deletes that fail because of foreign key constraints?
For example:
delete from table1 where value='something';
But, table2 has a foreign key that depends on the the value I want to delete in table1, so the delete fails. I'd like to do something like this:
delete from table1 where value='something' on foreign key fail update some_other_value='something else';
I know that looks really weird, but I have a good reason for doing it (without getting into details, it has to do with versioning historical data that can't be destroyed in the event that a value is referenced elsewhere.) I can figure out how to do this with more than one query, of course, but I'd like to do it in a single query if I can. I'm pretty sure it's not possible, but I'd like to ask before giving up 🙂
Thanks!
Best Answer
You can't directly do it since there's no way (that I can think of) to alternately
UPDATE
orDELETE
from the same query... generally a query can only do one type of CRUD operation (ON DUPLICATE KEY UPDATE
being an exception to this).You could, however, make it "feel like" a single query, with a stored procedure, where you'd set up a
CONTINUE HANDLER
for the foreign key error you anticipate. The handler will trap the error and can be used to set a variable which you can test to see whether you hit the error and therefore need to also try the update query.This will try the delete; if it hits a foreign key error it will then try the update (and the foreign key error will be suppressed). The procedure will return a record set with the number of rows affected by each query and whether the foreign key error occurred. If the "input_value" isn't an
INT
or theWHERE
clause is more complex, you'll need to modify the structure, accordingly.The potential problem I see here is that if the where clause is too broad, and matches some rows that would cause a foreign key error and other rows that wouldn't, then the query will not treat the rows individually. That could be accomplished by a more sophisticated procedure that used a cursor to find the rows in the where clause and tried to delete or update them individually by primary key after identifying them. That approach would be less efficient but more precise.