MySQL – Command Denied for DELETE with JOIN

You can't directly do it since there's no way (that I can think of) to alternately UPDATE or DELETE 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.

DELIMITER $$

DROP PROCEDURE IF EXISTS delete_or_update $$
CREATE PROCEDURE delete_or_update (IN input_value INT)
BEGIN

  DECLARE hit_fk_error TINYINT DEFAULT 0;
  DECLARE deleted_row_count INT DEFAULT NULL;
  DECLARE updated_row_count INT DEFAULT NULL;

  -- 1451 is probably the error code that will be generated
  -- Cannot delete or update a parent row: a foreign key constraint fails (%s)
  -- otherwise, you may need to substitute the correct error code in the next line

  DECLARE CONTINUE HANDLER FOR 1451 SET hit_fk_error = TRUE;

  DELETE FROM parent_table WHERE tested_column = input_value; /* arg to the stored proc */
  SET deleted_row_count = ROW_COUNT();

  IF (hit_fk_error = TRUE) THEN
    UPDATE parent_table SET some_other_value = 'something_else'
     WHERE tested_column = input_value;
    SET updated_row_count = ROW_COUNT();
  END If;

  SELECT deleted_row_count, updated_row_count, hit_fk_error;

END $$

DELIMITER ;

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 the WHERE 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.

Here are my notices:

1. Make sure that creator exists only in table1, or add [... WHERE table1.creator=..]
2. It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key.
3. From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned 'long list of fields..'). Try to execute the query by selecting one field only, or be select count(*) and see if there is a significant change
4. Note that 400*270=108000, which is not a tiny number of scanned rows :)