how to update a field with the value from from another table with a subquery? [closed]

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.

Answer

Your approach is not far off, but you need to correlate the subquery on titles to the employees table:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *