Error in running SQL Query in PHPmyadmin

Question

Table Schema:

Employee (empid, emp_name, age, DOB, designation, doj, basic_salary, salary_amt, medical_allowance, pension, GPFund, TA, House_allowance)
Monthly_salary (id, empid, year, month, total_salary)  

Query:

UPDATE `monthly_salary` 
     SET `total_salary`=`employee`.'salary_amt' -`employee`.'medical_allownces' - `employee`.'pension' 
     WHERE `employee`.'empid'=`monthly_salary`.'emp_id'

I'm trying to run this Query in phpmyadmin but its not running and giving errors. Employees empid is used as foreign key in monthly-salary table.

Answer 1

Remove the single qoutes '

Should looke like this

UPDATE `monthly_salary` salary
JOIN    `employee`      employee    ON employee.empid=salary.emp_id
SET     salary.total_salary=employee.salary_amt - employee.medical_allownces - employee.pension

I gave the tables alias names, makes it easier to read.

Answer 2

join in update is not used in your query.

UPDATE monthly_salary a
    JOIN employee b  ON  b.empid = a.emp_id
SET a.total_salary=b.salary_amt - b.medical_allownces - b.pension;