Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
see this SO answer? –  Jack Douglas Sep 27 '11 at 15:36
add comment

3 Answers

up vote 3 down vote accepted

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);
share|improve this answer
    
Nice. implicit temporary table > explicit temp table on my end. +1 –  Derek Downey Sep 27 '11 at 15:51
    
thanks Rolando, this also helped me to understand the basics of INNER JOINs as well. –  Maglebolia Sep 28 '11 at 7:37
add comment

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'; 
share|improve this answer
    
Your query matches the one in my answer. Mine is just refactored. Your answer would still work. +1 !!! –  RolandoMySQLDBA Sep 27 '11 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 '11 at 16:07
    
I would still use Rolandos version, since his example is well formatted and easy to scale (multiple JOINs) –  Maglebolia Sep 28 '11 at 7:39
    
if you say so :) –  Jonathan Oct 1 '11 at 20:31
add comment

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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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