SQL Update Query

swaggrK
swaggrK used Ask the Experts™
on
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;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database and Application Developer
Commented:
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:
Select Col1
     , Col2 + Col3 as Sum23
     , Sum23/Col1 As Reault 
From Table1;

Open in new window

Access allows all-at-once processing. This means alias Sum23 is processed in the same select clause and is ready for additional calculation in the same clause.

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;

Open in new window

Will produce the same result similar those in access.

Mike

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial