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;

Open in new window

swaggrKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.