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

if you post code, including SQL or XML, please highlight those lines in question and use the "code" button (101 010) on the editor toolbar to nicely format those!! Makes it just soooo much easier to read and understand! — marc_s
– marc_s, Commented Mar 19, 2010 at 9:47
@joseph does it (this syntax) work in oracle? stackoverflow.com/questions/2476206/optimize-the-sql-query/… — garik
– garik, Commented Mar 19, 2010 at 10:29

Vincent Malgrat · Accepted Answer · 2010-03-19 09:48:32Z

5

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;

answered Mar 19, 2010 at 9:48

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashish Gupta Over a year ago

+1 - I didn't know you could do SET (job_id, salary) = SELECT (Job_id, Salary). Thank you.

Ashish Gupta Over a year ago

@Vincent, Is that syntax applicable for SQL server as well?

Collectives™ on Stack Overflow

Optimize the sql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related