sql query to get multiplication of two rows of two different tables and insert the answer to one of the two table

Question

I have two tables tblemployee, tblEmpSalary. I want to take the MonthlySalary form the tblEmployee and multiply it with the attendance in the tblEmpSalary and insert the answer to the tblEmpSalary table totalsalary column. Anyone kindly help me please.

tblEmployee
>EmpId
>Empname
>MonthlySalary

tblEmpSalary
>SalaryId
>EmpId
>Month
>Year
>AttendanceOfCurrentMonth
>TotalSalaryOfMonth

I want the TotalSalaryOfMonth to be the

multiplication of MonthlySalary and AttendanceOfCurrentMonth

for each specific employee

Edit your question and provide sample data, desired results, and a database tag. — Gordon Linoff, Mar 31 '17 at 10:50

score 1 · Answer 1 · answered Mar 31 '17 at 11:07

1

Try this

UPDATE tblEmpSalary set TotalSalaryOfMonth=e.MonthlySalary*s.AttendanceOfCurrentMonth
from tblEmployee e join tblEmpSalary s ON e.empId=s.empId

answered Mar 31 '17 at 11:07

Rupesh Pandey

71
5

Please mark any one the response as accepted answer when you find the solution..... And good that it worked.... enjoy – Rupesh Pandey Mar 31 '17 at 12:11

score 1 · Accepted Answer · edited May 23 '17 at 12:25

1

I think you are looking for Update based on Join conditions. This link already answers your question.

UPDATE A SET TotalSalaryOfMonth = AttendanceOfCurrentMonth * MonthlySalary FROM tblEmpSalary A INNER JOIN tblEmployee B ON A.EmpId = B.EmpId

edited May 23 '17 at 12:25

Community

1
1

answered Mar 31 '17 at 11:12

Nikhil Mathew

56
5

sql query to get multiplication of two rows of two different tables and insert the answer to one of the two table

2 Answers2