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?