Replace into with subquery

I am trying to use both replace into and subquery for the same table but getting error:

#1093 - You can’t specify target table ‘mytable’ for update in FROM clause

REPLACE INTO mytable (id, category_id, product_id, name, translation, spec_values, specification_head, left_filter) VALUES(271282, 100278823, 19869099, ‘Heade_Manuf’, ‘Brand’, ‘Suunto’,(select specification_head from mytable where category_id=100278823 and product_id=19869099 and name=‘Heade_Manuf’),(select left_filter from mytable where category_id=100278823 and product_id=19869099 and name=‘Heade_Manuf’))

I believe i can’t use same table in the same main and inner queries. Is there any way i can do this query?

Have you tried using an alias?

Something like

  [COLOR=#333333](select left_filter from[B] mytable as a[/B] where category_id=100278823 and product_id=19869099 and name='Heade_Manuf')[/COLOR][COLOR=#333333]

[/COLOR]

Excellent thanks it worked :slight_smile:

i know i’m late but you can also do it like this with one fewer call to the table –

REPLACE
  INTO mytable
     ( id
     , category_id
     , product_id
     , name
     , translation
     , spec_values
     , specification_head
     , left_filter )
SELECT 271282
     , 100278823
     , 19869099
     , 'Heade_Manuf'
     , 'Brand'
     , 'Suunto'
     , specification_head
     , left_filter
  FROM mytable
 WHERE category_id = 100278823
   AND product_id = 19869099
   AND name = 'Heade_Manuf'

I have about 3,00,000 records and it takes 10 hours to finish. Is there any way i can optimize further.

see post #3… how long did that one take?

Sorry which post #3

the post #3 in this thread

Yes. Try what @r937;

It is a better approach and that should take less time, me thinks