Update table with the highest value from another table in Mysql

Question

I have a table called employees, with the following fields and values:

nome       salário
 a            10
 b            100
 c            30
 d            40

I have a second table called function_more_caro, with the same fields as the previous one, but with no record.

nome       salário
 *           *

I need the table function_more_caro be always up-to-date with the data, from the table employees, of the individual who has the highest salary.

For example, with the data above the table function_more_caro would be as follows:

nome       salário
 b            100

I don’t want the table function_more_caro be always filled with new values, but yes up-to-date, that is, there will always be only 1 registration, which is of the most expensive employee.

I don’t know how to do this (I’m a beginner in this subject). Someone could help me?

Answer 1

Before you start, add 1 record to the worksio_more_caro table.

You first need to know which is the highest salary, so use the function max(), Since this will find the name, and finally to update only 1 record add "limit 1".

Follow the complete code:

update funcionario_mais_caro set salario = (select max(salario) from func
ionarios), nome = (select nome from funcionarios where salario = funcionario_mais_caro.salario) limit 1;