I have two tables, employees and titles. They both have a field called risk. I’d like to take the value of risk from the titles table and update all of the rows in the employees table that have the same title (called employee_title). I am trying to use a subquery, but all the examples I have found using a subquery, “set” the updated field to a constant. In my case I want to update it to a value found in the subquery.
update employees e set e.risk = t.risk where e.employee_title=(SELECT title from titles t)
So for example, if the risk for an engineer in the titles table is low, I’d like to set all of the employees who are engineers (in the employees table) to “low”. Can I do this?
I have researched this is the book The Complete Reference, SQL Third Edition, but page page 245 only shows the updated field being set to constant.
Your approach is not far off, but you need to correlate the subquery on
titles to the
UPDATE employees e SET risk = (SELECT t.risk FROM titles t WHERE t.title = e.employee_title);
MySQL also supports a very convenient update join syntax, q.v. the answer by @forpas.