• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

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
SELECT id AS customer_application_id,
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
/* Look for things of the form
loan_purpose: ([^\n]+)'
occurring after :step_1 */
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 =

Open in new window

1 Solution
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.

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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now