Update table with the highest value of another table in MySQL



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

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

    I have a second table called functionio_more_caro, with the same fields of the previous, but without any registration.

    nome       salário
     *           *
    

    I need the table functionio_more_caro always updated updated with data, table Staff, of the individual who has the highest salary.

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

    nome       salário
     b            100
    

    I don't want the table functionio_more_caro always filled with new values, but yes updated updated, 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 matter). Could anyone help me?



  • Before you start, add 1 registration to the table functionio_mais_caro.

    You first need to know which is the largest room, so use the max( function), so you find the name, and by the end to update only 1 record add "limit 1".

    Follow the full 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;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2