Update with Select

Question

Update with Select

Navigation

#1 by (3 votes)

1

I'm new to the forum and SQL. I use the MySQL Workbench DBMS to try to learn the syntax. Querying a site of tutorial, I was to carry out 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 an error and I have no idea how to solve it:

"Error Code: 1093. You can not specify target table 'actor' for update in FROM clause "

I made the separate select to see if I was wrong in the syntax, but it ran smoothly.

Thank you!

mysql sql

asked by anonymous 20.01.2016 / 18:30

1 answer

Require input text input with jQuery validator Is it possible to improve the writing of this code?

score 3 · Accepted Answer

The problem happens because MySQL does not allow you to update a table and use it to set the update criterion.

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.