Optimize the sql query

Question

UPDATE employees
   SET job_id = (SELECT job_id
                   FROM employees
                  WHERE employee_id = 205),
       salary = (SELECT salary
                   FROM employees
                  WHERE employee_id = 205)
 WHERE employee_id = 114;

This is the query i have been using. Here i use 2 subqueries but they have the same where condition.. The seek time is doubled.. Is there a way to optimize the whole query to a single subquery?

Thanks in advance

Answer 1

Hi Joseph,

you can remove a subquery if you update a set of columns:

UPDATE employees
   SET (job_id, salary) 
        = (SELECT job_id, salary FROM employees WHERE employee_id = 205)
 WHERE employee_id = 114;