Update with Select

Asked

Viewed 766 times

1

I’m new to the forum and SQL. I use the Mysql Workbench DBMS to try to learn the syntax. Consulting a tutorial site, went to perform an update using a select, follows command line:

update actor
set salario = salario * 1.1
where salario = (select min(salario) from actor)

But it returns me a mistake and I have no idea how to solve:

"Error Code: 1093. You can’t specify target table 'actor' for update in from clause"

I did select separately to see if I was wrong in the syntax, but ran smoothly.

Thank you!

mysql sql

1 answer

3


The problem is because Mysql does not allow you to update a table and use it to define the criteria for update.

I think this might solve your problem:

update actor
set salario = salario * 1.1
where salario in
(
  select salario from
  (
    select min(salario)
    from actor
  ) as minSalario
)

So, you create a temporary table so you can use it in the update.

  • What about the error? It would be interesting to explain the reason that triggers such error.

  • There was a boy who had already commented, but deleted, I will edit the answer.

  • It was me, but there was no basis, I did not find in the documentation this information.

  • I was right what you had said, but I’ll update it so other people know too.

  • Thank you very much!

  • You guys recommend some cool website for me to learn?

Show 1 more comment

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.