{"id":37567,"date":"2024-08-04T11:58:34","date_gmt":"2024-08-04T01:58:34","guid":{"rendered":"https:\/\/database.guide\/?p=37567"},"modified":"2024-09-17T07:00:39","modified_gmt":"2024-09-16T21:00:39","slug":"using-update-with-a-subquery-in-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/using-update-with-a-subquery-in-sql\/","title":{"rendered":"Using UPDATE with a Subquery in SQL"},"content":{"rendered":"\n

Ever found yourself needing to update a bunch of rows in your database, but the condition for the update depends on data from another table? That’s where UPDATE<\/a><\/code> with a subquery comes in handy. Let’s break it down.<\/p>\n\n\n\n\n\n\n\n

Sample Data<\/h2>\n\n\n\n

First, let’s set up some sample tables:<\/p>\n\n\n\n

-- Create and populate the departments table\nCREATE TABLE departments (\n    dept_id INT PRIMARY KEY,\n    dept_name VARCHAR(50),\n    budget DECIMAL(10, 2)\n);\n\nINSERT INTO departments (dept_id, dept_name, budget)\nVALUES \n    (1, 'HR', 500000),\n    (2, 'IT', 1000000),\n    (3, 'Sales', 750000);\n\n-- Create and populate the employees table\nCREATE TABLE employees (\n    emp_id INT PRIMARY KEY,\n    name VARCHAR(50),\n    dept_id INT,\n    salary DECIMAL(10, 2)\n);\n\nINSERT INTO employees (emp_id, name, dept_id, salary)\nVALUES \n    (1, 'Homer Simpson', 1, 60000),\n    (2, 'Peter Griffin', 2, 75000),\n    (3, 'Beatrice Johnson', 2, 65000),\n    (4, 'Becky Brown', 3, 80000),\n    (5, 'Charlie Davis', 3, 70000);<\/code><\/pre>\n\n\n\n
Now that we’ve got our tables set up, let’s start with a simple example. Say we want to give a 10% raise to all employees in the IT department.<\/p>\n\n\n\n
Start with a SELECT<\/code> Statement<\/h2>\n\n\n\n
When we use a subquery in the UPDATE<\/code> statement, oftentimes we can use the same subquery that we’d use if we were simply selecting the rows we want to update. <\/p>\n\n\n\n
Therefore, before going right ahead and writing the UPDATE<\/code> statement, and then immediately running that against the database, it’s usually a good idea to start by selecting the data with a SELECT<\/code> statement first. That way if we’ve made a mistake in our query logic, we can catch it and correct it before it’s too late.<\/p>\n\n\n\n
So let’s go ahead with a SELECT<\/code> statement:<\/p>\n\n\n\n
SELECT * FROM employees WHERE dept_id = 2;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
emp_id  name              dept_id  salary
------  ----------------  -------  ------
2       Peter Griffin     2        75000 
3       Beatrice Johnson  2        65000 <\/pre>\n\n\n\n
OK, so there are two employees in the IT department.<\/p>\n\n\n\n
But wait! What if we don’t know the department ID for IT?<\/p>\n\n\n\n
Well, that’s where the subquery comes in:<\/p>\n\n\n\n
SELECT * FROM employees \nWHERE dept_id = (\n    SELECT dept_id \n    FROM departments \n    WHERE dept_name = 'IT'\n    );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
emp_id  name              dept_id  salary
------  ----------------  -------  ------
2       Peter Griffin     2        75000 
3       Beatrice Johnson  2        65000 <\/pre>\n\n\n\n
Same result, as expected. All we needed to know was the department name, and the subquery went to work and dug up the corresponding ID for us. That was passed to the outer query and so the query was able to return the intended results.<\/p>\n\n\n\n
So the same concept would apply if we wanted to update the table. We could use a subquery to identify the ID of the IT department.<\/p>\n\n\n\n
So to reiterate, the good thing about running a SELECT<\/code> statement first is that we can see which rows are going to be updated before we run the UPDATE<\/code> statement. That way, if we’ve made a mistake in our query, we can correct it before updating any rows and making a complete mess of things.<\/p>\n\n\n\n
Convert it to an UPDATE<\/code> Statement<\/h2>\n\n\n\n
So we can now take our SELECT<\/code> statement from above and modify the first couple of lines to convert it into an UPDATE<\/code> statement:<\/p>\n\n\n\n
UPDATE employees\nSET salary = salary * 1.1\nWHERE dept_id = (\n    SELECT dept_id \n    FROM departments \n    WHERE dept_name = 'IT'\n    );<\/code><\/pre>\n\n\n\n
So just to be sure, this subquery finds the ID of the IT department and then the outer UPDATE<\/code> statement uses that ID in its WHERE<\/code> clause.<\/p>\n\n\n\n
Let’s check the result of the update:<\/p>\n\n\n\n
SELECT * FROM employees \nWHERE dept_id = (\n    SELECT dept_id \n    FROM departments \n    WHERE dept_name = 'IT'\n    );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
emp_id  name              dept_id  salary
------  ----------------  -------  ------
2       Peter Griffin     2        82500 
3       Beatrice Johnson  2        71500 <\/pre>\n\n\n\n
Nice to see that they’ve received their well deserved raise!<\/p>\n\n\n\n
Another UPDATE<\/code> with a Subquery<\/h2>\n\n\n\n
Let’s kick it up a notch. Suppose we want to give a 5% raise to all employees in departments with a budget over 700,000. Here’s how we can do that:<\/p>\n\n\n\n
UPDATE employees\nSET salary = salary * 1.05\nWHERE dept_id IN (SELECT dept_id FROM departments WHERE budget > 700000);<\/code><\/pre>\n\n\n\n
In this case, our subquery might return multiple department IDs, so we use the IN<\/code> operator<\/a> instead of the equals operator (=<\/code>)<\/a>.<\/p>\n\n\n\n
Let’s take a look at the data now:<\/p>\n\n\n\n
SELECT \n    e.name, \n    d.dept_name, \n    e.salary \nFROM employees e\nINNER JOIN departments d ON e.dept_id = d.dept_id;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
name              dept_name  salary
----------------  ---------  ------
Homer Simpson     HR         60000 
Peter Griffin     IT         86625 
Beatrice Johnson  IT         75075 
Becky Brown       Sales      84000 
Charlie Davis     Sales      73500<\/pre>\n\n\n\n
So the only person not to receive a raise was Homer Simpson from the HR department. <\/p>\n\n\n\n
More Subqueries<\/h2>\n\n\n\n
Check out my article called Understanding the SQL Subquery<\/a> that goes over various types of subqueries, along with examples. <\/p>\n","protected":false},"excerpt":{"rendered":"
Ever found yourself needing to update a bunch of rows in your database, but the condition for the update depends on data from another table? That’s where UPDATE with a subquery comes in handy. Let’s break it down.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[15,10,173],"class_list":["post-37567","post","type-post","status-publish","format-standard","hentry","category-sql","tag-create-query","tag-how-to","tag-subqueries"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37567","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=37567"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37567\/revisions"}],"predecessor-version":[{"id":37576,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37567\/revisions\/37576"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=37567"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=37567"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=37567"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}