SQL Update Query
Can someone explain to me what this update query is doing?
UPDATE customer_applications
SET loan_purpose = fetch_loan_purpose_subquery.loan_purpose
FROM (
SELECT id AS customer_application_id,
CASE
WHEN loan_purpose_from_step_1_subquery.loan_purpose is null
THEN substring( substring(information FROM
': &' || substring(information from E':step_1: \\*([0-9]+)') ||
'.*:step_1:' ) from E'loan_purpose: ([^\n]+)')
ELSE loan_purpose_from_step_1_subquery.loan_purpose
END AS loan_purpose
FROM (
SELECT
customer_applications.id,
customer_applications.information,
/* Look for things of the form
loan_purpose: ([^\n]+)'
occurring after :step_1 */
substring(
substring(customer_applications.information FROM E':step_1.*')
FROM E'loan_purpose: ([^\n]+)')
AS loan_purpose
FROM customer_applications
INNER JOIN loans ON loans.customer_application_id = customer_applications.id
ORDER BY id DESC) loan_purpose_from_step_1_subquery
) fetch_loan_purpose_subquery
WHERE customer_applications.id =
fetch_loan_purpose_subquery.customer_application_id;
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Please take a look at #3 in: http://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html
This explains how a derived expression works. Your update query is using derived expression.
But why we need to use derived expressions?
In Ms Access the following query works but not in SQL Server:
SQL Server doesn't allow all-at-once processing. But because the order of processing of SQL clauses in SQL Server is:
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause
We could rewrite the example above in the following order:
Mike
This explains how a derived expression works. Your update query is using derived expression.
But why we need to use derived expressions?
In Ms Access the following query works but not in SQL Server:
Select Col1
, Col2 + Col3 as Sum23
, Sum23/Col1 As Reault
From Table1;
SQL Server doesn't allow all-at-once processing. But because the order of processing of SQL clauses in SQL Server is:
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause
We could rewrite the example above in the following order:
Select Col1
, Sum23
, Sum23/Col1 As Reault
From (Col1
, Col2 + Col3 as Sum23
From Table1
) As D;
Mike
Premium Content
You need an Expert Office subscription to comment.Start Free Trial