Not able to create a query on mysql

Question

update email_template_mapping
    set template_footer = (select id
                           from email_template_mapping
                           where template_description = 'footer'
                          );

this query giving me error You can't specify target table 'email_template_mapping' for update in FROM clause.

can you please help. Thanks in advance.

Possible duplicate of [MySQL Error 1093 - Can't specify target table for update in FROM clause](https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — Ricardo Pontual, Jun 19 '18 at 11:41

score 2 · Accepted Answer · answered Jun 19 '18 at 11:40

2

In MySQL, you need to express this using JOIN:

update email_template_mapping etm left join
       email_template_mapping etmf
       on etmf.template_description = 'footer'
    set etm.template_footer = etmf.id;

answered Jun 19 '18 at 11:40

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Jun 19 '18 at 11:40

1

update email_template_mapping
set template_footer =  id
where template_description = 'footer'

should look something like this, it looks like in that subquery youre using the same table so you can just set the template footer = id and then use the where after. Look up the format for an update statement and that will point you in the correct direction too, hope that helps! :)

answered Jun 19 '18 at 11:40

t..

1,101
1
9
22

That would set the footer reference of the footer template to itself. But It seems like the OP wants to set the footer reference of all templates to the footer template. – sticky bit Jun 19 '18 at 11:58
@sticky- yes you are right. above query updating only one row. i need to update all the rows. – swayam swayam Jun 19 '18 at 12:09
Thank you so much for the help @Static – swayam swayam Jun 19 '18 at 12:39

Not able to create a query on mysql

2 Answers2