Help with update query

[egstatus]

Hi all, I need help with the following query.

I have a view [V_GEN_EMPLEADOS_CON_SALARIO_ZERO] that return all the employees with salary being equal to zero.
[There are 4 different companies within the corporation, so the view return records from each of those companies.]

This is the view code:

SELECT        CORR_EMPRESA, CORR_EMPLEADO, NOMBRE_EMPLEADO, DOCUMENTO_IDENTIDAD, CORR_TIPO_EMPLEADO, FECHA_INGRESO, SEXO, 
                         DIRECCION_PARTICULAR, SALARIO_MENSUAL, CORR_PUESTO, ESTADO_ACTIVO
FROM            dbo.GEN_EMPLEADO
WHERE        (SALARIO_MENSUAL = 0.0)

I have been asked to update the salary of such employees based on the average monthly salary of other employees within the same company and the same position.

The following query returns the average salary for employees for the same company and same position:

SELECT A.CORR_EMPRESA, B.CORR_PUESTO, B.NOMBRE_PUESTO,  AVG(A.SALARIO_MENSUAL) AS AVERAGE_SALARIO_MENSUAL
FROM PLA_DOCUMENTO_DETA AS A
INNER JOIN PLA_PUESTO AS B ON A.CORR_EMPRESA = B.CORR_EMPRESA
INNER JOIN GEN_EMPLEADO AS C ON A.CORR_EMPRESA=C.CORR_EMPRESA AND A.CORR_EMPLEADO=C.CORR_EMPLEADO AND B.CORR_PUESTO=C.CORR_PUESTO
WHERE B.CORR_PUESTO IN (SELECT DISTINCT CORR_PUESTO
	FROM V_GEN_EMPLEADOS_CON_SALARIO_ZERO
	WHERE CORR_PUESTO IS NOT NULL) 
	AND A.CORR_EMPRESA IN (SELECT DISTINCT CORR_EMPRESA
	FROM V_GEN_EMPLEADOS_CON_SALARIO_ZERO
	WHERE CORR_EMPRESA IS NOT NULL)
GROUP BY B.CORR_PUESTO, B.NOMBRE_PUESTO, A.CORR_EMPRESA

My problem is that I don't know how to update the salary for the records returned by the view:
I'm thinking something like:

UPDATE V_GEN_EMPLEADOS_CON_SALARIO_ZERO
SET SALARIO_MENSUAL = AVERAGE_SALARIO_MENSUAL

AVERAGE_SALARIO_MENSUAL is a field calculated on the second query.

Can some one please tell me what is that I'm doing wrong or point me in the right direction?

Thanks in advance.

Ed

 

[TamarGranor]

You're on the right track. What you need is to join the two tables, something like:

UPDATE V_GEN_EMPLEADOS_CON_SALARIO_ZERO
SET SALARIO_MENSUAL = AVERAGE_SALARIO_MENSUAL 
FROM YourAveragingView
WHERE <the company and position match>

I can't make it more specific because my Spanish isn't good enough to tell which are the relevant fields.

Tamar