SQL Update Query

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;

Select allOpen in new window