In this tutorial, you will learn how to use MySQL UPDATE statement to update data in a table. Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL. Using a Correlated Subquery in a T SQL Statement Database. Journal. com. In last. I discussed what and how to use a subquery in a T SQL. This month I will expand on this subject by discussing correlated. I will explain what a correlated subquery is, and show a number of. T SQL statement. What is a Correlated Subquery A correlated subquery is a SELECT statement nested inside. T SQL statement, which contains a reference to one or more columns in. Therefore, the correlated subquery can be said to be. This is the main difference between a correlated. A plain subquery is not dependent on the. A correlated subquery, since it is dependent on the outer query. A correlated subquery will be executed many times while. T SQL statement that contains the correlated subquery. The. correlated subquery will be run once for each candidate row selected by the. The outer query columns, referenced in the correlated subquery. Depending. on the results of the execution of the correlated subquery, it will determine. Using a Correlated Subquery in a WHERE Clause. Suppose you want a report of all Order. IDs where. the customer did not purchase more than 1. This way you could review these orders, and possibly contact. A correlated subquery in a WHERE clause can help you produce this. Here is a SELECT statement that produces the desired list of Order. IDs. select distinct Order. Id. from Northwind. Order Details OD. Quantity ltl select avgQuantity. Northwind. dbo. Order Details. OD. Product. ID Product. ID. The correlated subquery in the above command is contained. WHERE clause. above. Here you can see this correlated subquery contains a reference to OD. Product. ID. This reference compares the outer querys Product. ID with the inner. Mysql Update Statement With Subquery' title='Mysql Update Statement With Subquery' />This MySQL tutorial explains how to use the MySQL UPDATE statement with syntax and examples. The MySQL UPDATE statement is used to update existing records in a table. UPDATE is a DML statement that modifies rows in a table. Singletable syntax UPDATE LOWPRIORITY IGNORE tablereference SET assignmentlist WHERE where. Product. ID. When this query is executed, the SQL engine. Order. Details record. This inner query will calculate the average Quantity. Product. ID for the candidate row being processed. MySQL Community Edition is a freely downloadable version of the worlds most popular open source database that is supported by an active community of open source. This correlated subquery determines if the inner query. WHERE clause. If it does, the. T SQL SELECT statement. The code below is another example that uses a correlated. WHERE clause to display the top two customers, based on the. You might want to. Company. Name, Contact. Name, Address. City, Country, Postal. Code from Northwind. Customers Outer. C. Customer. ID in. Inner. C. Customer. Id. from Northwind. Order Details OD join Northwind. Orders O. on OD. Order. Id O. Order. ID. Northwind. Customers Inner. C. O. Customer. ID Inner. C. Customer. Id. Where Region Outer. C. Region. group by Region, Inner. C. Customer. Id. order by sumUnit. Price Quantity 1 Discount desc. Region. Here you can see the inner query is a correlated subquery. Outer. C, which is the table alias for the Northwind. DBO. Customer. table in the outer query. This inner query uses the Region value. If the Customer. ID of the outer. Correlated Subquery in the HAVING Clause. Say your organizations wants to run a yearlong incentive. Therefore, they advertise to your customers that. Below. is an example of how to calculate the rebate amount. This example uses a correlated. HAVING clause to identify the customers that qualify to receive. Here is my code for this query. C. Customer. ID, Count7. Rebate. from Northwind. DBO. Customers C. Northwind. DBO. Orders O. Customer. ID O. Customer. ID. where Datepartyy,Order. Date 1. 99. 8. C. Customer. Id. having 7. ALLselect sumUnit. Price Quantity 1 Discount. Northwind. DBO. Orders O. Northwind. DBO. Order Details OD. O. Order. ID OD. Order. ID. where Customer. ID C. Customer. Id. Datepartyy,Order. Date 1. 99. 8. O. Order. Id. By reviewing this query, you can see I am using a correlated. HAVING clause to calculate the total order amount for each. I use the Customer. ID from the outer query and the. Datepartyy,Order. Date, to help identify the. Order records associated with each customer, that were placed the year 1. For these associated records I am calculating the total order amount, for each. Order Details records, using the. Unit. Price Quantity 1 Discount. Connection Microsoft Exchange Server Unavailable 2010 Dodge there. If each and. every order for a customer, for year 1. I then calculate the Rebate amount in the outer query using this. Count7. 5. SQL Servers query engine will only execute the inner correlated. HAVING clause for those customer records identified in the. Performing an Update Statement Using a Correlated Subquery. A correlated subquery can even be used in an update. Here is an example. AA int, S int. create table BA int, B int. AA values1. insert into AA values2. AA values3. insert into B values1,1. B values2,1. insert into B values2,1. B values3,1. insert into B values3,1. B values3,1. set S select sumB. B. where A. A A group by A. A. Here is the result set I get. I run this query on my machine. A S. In my query above, I used the correlated subquery to update. A in table A with the sum of column B in table B for rows that have the. A as the row being updated. Conclusion. Let me summarize. A subquery and a correlated subquery are. SELECT queries coded inside another query, known as the outer query. The. correlated subquery and the subquery help determine the outcome of the result. A subquery, when executed independent of. Where as, a correlated subquery cannot be executed. I hope that you now understand the different of. T SQL. See All Articles by Columnist. Gregory A. Larsen.