I've just tried to convert this update command, which is Oracle sql, to MySQL with SQLWays and it failed with the following error:
You can't specify target table 'animal_table' for update in FROM clause
source command (Oracle):
UPDATE animal_table
SET (animal_info1,
animal_info2,
animal_info3) = (SELECT animal_nbr, animal_amount, '00'
FROM animal_table t2
WHERE t2.category_id = animal_table.category_id
AND t2.sumary_id = animal_table.summary_id
AND t2.animal_type = 'special'
)
WHERE animal_type = 'cats'
AND category_id = 'foo';
suggested target command (MySQL):
UPDATE animal_table
SET
animal_info1 = (SELECT animal_nbr FROM animal_table t2
WHERE t2.category_id = animal_table.category_id
AND t2.sumary_id = animal_table.summary_id
AND t2.animal_type = 'special'),
animal_info2 = (SELECT animal_amount FROM animal_table t2
WHERE t2.category_id = animal_table.category_id
AND t2.sumary_id = animal_table.summary_id
AND t2.animal_type = 'special'),
animal_info3 = (SELECT '00' FROM animal_table t2
WHERE t2.category_id = animal_table.category_id
AND t2.sumary_id = animal_table.summary_id
AND t2.animal_type = 'special')
WHERE animal_type = 'cats'
AND category_id = 'foo';
can anyone help me out here?
Best Answer
Try using an UPDATE JOIN of the table against itself
Make sure that animal_table has a compound index on category_id and summary_id. If you do not have such an index, please run this:
UPDATE 2011-09-27 13:10 EDT
I just noticed that categrory_id 'foo' limits the dataset. Here is an updated refactoring of my answer ( I moved category_id = foo into the subquery )
I also recommend adding this additional index to accommodate the subquery