Using a complex query results to update a table

Question

I have the following query...

WITH NoEndJobs AS (
    SELECT
        CustomerID,
        JobType,
        Book,
        Page,
        NextDocBook = LEAD(Job.Book) OVER (PARTITION BY CustomerID, JobType ORDER BY Book, Page),
        NextDocPage = LEAD(Job.Page) OVER (PARTITION BY CustomerID, JobType ORDER BY Book, Page)
    FROM JobList Job
    WHERE EndBook='' OR EndPage=''
)
SELECT
    NoEndJobs.CustomerID,
    NoEndJobs.JobType,
    NoEndJobs.Book,
    NoEndJobs.Page,
    coalesce(LastJobInfo.Book, '') AS EndBook,
    coalesce(LastJobInfo.Page, '') AS EndPage
FROM NoEndJobs
LEFT JOIN
(
    SELECT
        CustomerID,
        JobType,
        Book,
        Page,
        PrevEntry
    FROM JobEntries
) NextJobEntry ON
    NextJobEntry.CustomerID=NoEndJobs.CustomerID AND
    NextJobEntry.JobType=NoEndJobs.JobType AND
    NextJobEntry.Book=NoEndJobs.NextDocBook AND
    NextJobEntry.Page=NoEndJobs.NextDocPage
LEFT JOIN
(
    SELECT
        ID,
        Book,
        Page
    FROM JobEntries
) LastJobInfo ON LastJobInfo.ID=NextJobEntry.PrevEntry
ORDER BY
    NoEndJobs.CustomerID,
    NoEndJobs.JobType,
    NoEndJobs.Book,
    NoEndJobs.Page;

I want to be able to update a table named JobList with the resulting SELECT rows.

I would like to update the table based off of the CustomerID, JobType, Book and Page. For example... The first row results from the select might be something like...

So I want to do something like this with the results..

UPDATE JobList
    SET JobList.EndBook=RESULTS.EndBook, JobList.EndPage=RESULTS.EndPage
WHERE 
    JobList.CustomerID=RESULTS.CustomerID AND
    JobList.JobType=RESULTS.JobType AND
    JobList.Book=RESULTS.Book AND
    JobList.Page=RESULTS.Page

If I'm not clear on what I'm trying to do I can go into more detail if requested. Basicly I want to take my results and update the table that matches the data set IF EndBook and EndPage fields are not BLANK ('').

I have tried to do a few different things but always get syntax errors never able to make it work. The query above is solid and works well though!

Answer 1

You can do it in the following two steps:

Step 1: Store the result into a [Result]

WITH NoEndJobs AS ( /* this part does not change */
   ... /* TL, NR */
)

WITH [Result] as(  /* the rest part goes to here */
   ... /* TL, NR */
)

Step 2: Merge the [Result] into the [JobList]:

Merge
    [JobList] as TARGET
Using
    [Result] as SOURCE
On
    (TARGET.CustomerID=RSOURCE.CustomerID AND
    TARGET.JobType=SOURCE.JobType AND
    TARGET.Book=SOURCE.Book AND
    TARGET.Page=SOURCE.Page)
When MATCHED
    Then Update Set 
        TARGET.EndBook = SOURCE.EndBook,
        TARGET.EndPage=SOURCE.EndPage

Answer 2

You can update the CTE itself; you just need to include the columns you are updating:

WITH NoEndJobs AS (
    SELECT
        CustomerID,
        JobType,
        Book,
        Page,
        EndBook,
        EndPage,
        NextDocBook = LEAD(Job.Book) OVER (PARTITION BY CustomerID, JobType ORDER BY Book, Page),
        NextDocPage = LEAD(Job.Page) OVER (PARTITION BY CustomerID, JobType ORDER BY Book, Page)
    FROM JobList Job
    WHERE EndBook='' OR EndPage=''
)
UPDATE NoEndJobs
SET
  EndBook = coalesce(LastJobInfo.Book, ''),
  EndPage = coalesce(LastJobInfo.Page, '') 
FROM NoEndJobs
LEFT JOIN
(
    SELECT
        CustomerID,
        JobType,
        Book,
        Page,
        PrevEntry
    FROM JobEntries
) NextJobEntry ON
    NextJobEntry.CustomerID=NoEndJobs.CustomerID AND
    NextJobEntry.JobType=NoEndJobs.JobType AND
    NextJobEntry.Book=NoEndJobs.NextDocBook AND
    NextJobEntry.Page=NoEndJobs.NextDocPage
LEFT JOIN
(
    SELECT
        ID,
        Book,
        Page
    FROM JobEntries
) LastJobInfo ON LastJobInfo.ID=NextJobEntry.PrevEntry;

SELECT * 
FROM JobList 
ORDER BY
    CustomerID,
    JobType,
    Book,
    Page;

SQL Fiddle