New answers tagged delete
Your trigger tries to do something that cannot be done in MySQL. You cannot use an SQL statement (DELETE, in your case) on the table that is associated to the trigger. You will get an error like this: ERROR 1442 (HY000): Can't update table 't' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. ...
That Trigger worked. CREATE TRIGGER trgCustomer ON customer INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON DELETE FROM customer WHERE customer.ID IN (Select ID from deleted where name != 'Peter') END;
You have two options: 1) Rollback the DELETE in case you're deleting the row "Peter". You do this with an AFTER trigger. Be aware that it will rollback the whole transaction in case yuo're deleting multiple rows among which at least one is "Peter". CREATE TRIGGER TR_CUSTOMER_DEL ON customer FOR DELETE AS BEGIN SET NOCOUNT ON; IF EXISTS ( ...
Put the SELECT INTO OUTFILE and TRUNCATE (or DROP) TABLE in a stored procedure; then CALL the SP. (Caveat: I am not sure if those two statements can be performed in a SP.)
Use pt-archiver tool. It can delete data that it moves. Alternatively use drop & create after the select into outfile.
You can use Jailer tool, it will find all rows of child tables that reference to master table. Example: I have 4 tables : employee, employee_detail, phone_address, relationship. I want to delete employee which name="JOHN". With Jailer, it will find rows in employee_detail & relationship which FK to "JOHN" (by id). And because of phone_address reference ...
why is the query optimizer not able to use the index for deleting when the subquery version, while it is while using the join version? Because the optimizer is/was a bit dumb in that regard. Not only for DELETE and UPDATE but for SELECT statements as well, anything like WHERE column IN (SELECT ...) was not fully optimized. The execution plan usually ...
here is the answers for two of your questions Optimizer is not able to use index because the where clause changes for every row. The delete statement will look something like this after it pass the optimizer delete from VARIABLE_SUBSTITUTION where EXISTS ( select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY where BUILDRESULTSUMMARY.BUILD_KEY = ...
Top 50 recent answers are included