3

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?

1

3 Answers 3

3

Try using an UPDATE JOIN of the table against itself

UPDATE
    animal_table A
INNER JOIN
(
    SELECT
        category_id,summary_id,
        animal_nbr,animal_amount,'00' info3
    FROM animal_table
    WHERE animal_type = 'special'
) B USING (category_id,summary_id)
SET
    A.animal_info1 = B.animal_nbr,
    A.animal_info2 = B.animal_amount,
    A.animal_info3 = B.info3
WHERE
    A.animal_type = 'cats' AND
    A.category_id = 'foo';

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:

ALTER TABLE animal_table ADD INDEX (category_id,summary_id);

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 )

UPDATE
    animal_table A
INNER JOIN
(
    SELECT
        category_id,summary_id,
        animal_nbr,animal_amount,'00' info3
    FROM animal_table
    WHERE animal_type = 'special'
    AND category_id = 'foo'
) B USING (category_id,summary_id)
SET
    A.animal_info1 = B.animal_nbr,
    A.animal_info2 = B.animal_amount,
    A.animal_info3 = B.info3
WHERE
    A.animal_type = 'cats';

I also recommend adding this additional index to accommodate the subquery

ALTER TABLE animal_table ADD INDEX (animal_type,category_id);
2
  • Nice. implicit temporary table > explicit temp table on my end. +1 Sep 27, 2011 at 15:51
  • thanks Rolando, this also helped me to understand the basics of INNER JOINs as well.
    – Ed Michel
    Sep 28, 2011 at 7:37
1

I would suggest this, but please test it before running on some sample data:

UPDATE animal_table t1 inner join animal_table t2
ON t2.category_id = t1.category_id 
AND t2.sumary_id = t1.summary_id 
AND t2.animal_type = 'special'
SET t1.animal_info1 = t2.animal_nbr, 
t1.animal_info2 = t2.animal_amount, 
t1.animal_info3 = '00'
where t1.animal_type = 'cats'
and t1.category_id = 'foo'; 
4
  • Your query matches the one in my answer. Mine is just refactored. Your answer would still work. +1 !!! Sep 27, 2011 at 15:57
  • 1
    Actually, I would like the original poster to check if it does. Joins do not always equal the data from sub queries.
    – Jonathan
    Sep 27, 2011 at 16:07
  • I would still use Rolandos version, since his example is well formatted and easy to scale (multiple JOINs)
    – Ed Michel
    Sep 28, 2011 at 7:39
  • if you say so :)
    – Jonathan
    Oct 1, 2011 at 20:31
1

Unfortunately, in MySQL, you cannot update a table that you've listed in a subquery. From the docs (at the end):

Currently, you cannot update a table and select from the same table in a subquery.

You can nest the subqueries like so (untested):

UPDATE animal_table
SET 
animal_info1 = (SELECT animal_nbr FROM (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') as X),
animal_info2 = (SELECT animal_amount FROM (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') AS Y),
animal_info3 = (SELECT '00' FROM (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')AS Z)
WHERE animal_type = 'cats'
AND category_id = 'foo';

But I think Rolando's is cleaner, since it doesn't duplicate the subqueries.

3
  • +1 for explaining why the original query does not work due to subquery headaches. Sep 27, 2011 at 15:49
  • Isn't the suggested target command containing temporary tables in subquery? If not, can you please add that example command please?
    – Ed Michel
    Sep 27, 2011 at 15:50
  • @DTest - Remember we dealt with subquery issues in a past question ? dba.stackexchange.com/q/1371/877 Sep 27, 2011 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.