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

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

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.

LVL 34

Mike EghtebasDatabase and Application DeveloperCommented: 2015-10-04

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

SQL Update Query

Who is Participating?