Mysql UPDATE with JOIN and WHERE

Asked

Viewed 1,611 times

1

My tables 

EMPREGADO   Cod_Emp, Nome_Emp
COMPANHIA   Cod_Comp, Nome_Comp
TRABALHA    Cod_Emp, Cod_Comp, Salário, Cod_Emp_Supervisor

I want to give a 10% increase to all company supervisors' test'.

UPDATE trabalha 
INNER JOIN companhia ON trabalha.Cod_Comp = companhia.Cod_Comp AND companhia.Nome_comp = 'teste'
SET salario = salario + (salario*0.10)
WHERE Cod_Emp IN (SELECT Cod_Emp_Supervisor FROM trabalha)
mysql sql

1 answer

1


UPDATE
    TRABALHA T
INNER JOIN
    COMPANHIA C ON 
    (
        C.Cod_Comp = T.Cod_Comp
        AND
        C.Nome_comp = 'Teste'
        AND
        T.Cod_Emp = T.Cod_Emp_Supervisor
    )
SET
    T.Salario = T.Salario + (T.Salario * 0.10)

From what I understand the column Cod_emp_supervisor in the WORK table indicates who is the supervisor of each employee within the COMPANY.

As I do not know the base or the system that loads in it. I imagine that the Cod_emp_supervisor field will be filled with the supervisor’s own code. Or if it is not filled is null, just replace the clause T.Cod_emp = T.Cod_emp_supervisor for T.Cod_emp_supervisor IS NULL.

  • I did it this way, but it’s not quite what I wanted. I was trying to check with the WHERE the Cod_emp with the Cod_emp_supervisor, so I would know that that employee is a supervisor. And then check the company that he works with JOIN.

  • @vdschuck In the WORK table the supervisor type employee the Cod_emp_supervisor field is null or with the same code as the Cod_emp field?

  • That’s why I put the clause on JOIN T.Cod_emp = T.Cod_emp_supervisor to obtain only the supervisors.

Browser other questions tagged mysql sql

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