Update table with the highest value of another table in MySQL

Question

Update table with the highest value of another table in MySQL

Navigation

#1 by (1 votes)

1

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

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

I have a second table called official_more_caro , with the same fields as above, but with no records.

nome       salário
 *           *

I need the official_material table to always be updated with data from the employees table of the individual with the highest salary. p>

For example, with the above data the table official_more_caro would look like this:

nome       salário
 b            100

I do not want the official_name_table table to always be populated with new values, but updated , ie there will always be only 1 record, who is the most expensive employee.

I do not know how to do this (I'm a beginner on this subject). Could someone help me?

mysql sql-update

asked by anonymous 14.12.2017 / 01:14

1 answer

Error importing xls into postgres Connect SpringBoot with PostgreSQL

score 1 · Accepted Answer

Before you begin, add 1 record to the official_table_table table.

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

Here is 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;