Bala Krishnan February 2016

How to update values of an entire column in a table with respect to values of a column in another table with foreign key : MYSQL

I have a table named hours_worked which has two columns, employeeID(primary key) and worked_hours_for_a_month. I have another table salary, which has two columns with attributes employeeID(foreign key of hours_worked), salary. Now i want to update the salary attribute column with values from the hours_worked(worked_hours_of_a_month) * 150 by matching the employeeID from hours_worked and salary tables. I want to update the entire column in one strike. Possible?

Answers


Jimish Gamit February 2016

update salary s 
    left join hours_worked hw on hw.employeeID = s.employeeID 
set 
    s.salary = hw.worked_hours_for_a_month*150

cheers :)


Webster February 2016

you can use this SQL Query

UPDATE salary s
LEFT JOIN hours_worked hw ON
    hw.employeeID = s.employeeID
SET
    s.salary = hw.worked_hours_of_a_month * 150

Here's the reference for How to update table's column value based on another table's column value

Post Status

Asked in February 2016
Viewed 1,248 times
Voted 8
Answered 2 times

Search




Leave an answer