CODEWITHSUNDEEP
sqlsql-server
MattTheHack
MattTheHack

Reputation: 1384

Setting a column value to the previous records value

I have a query where I want to select a record, and the record that becomes before and after it based on an id. For all NULL records for FirstName, I want to set the FirstName to be that of the previous record. I have code that successfully gets a table of values before and after, but how can I update the value? I've tried the code below but I get an error like this

The multi-part indentifier "CTE.FirstName" could not be bound

Anyone know what the issue is? Please note, I'm using SQL Server 2008 so i can't use LAG or LEAD 

 WITH CTE AS (
    SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
    p.FirstName
    FROM Person.Person p
    )
    SELECT
    prev.FirstName PreviousValue,
    CTE.FirstName,
    nex.FirstName NextValue
    FROM CTE
    LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
    LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
    IF(CTE.FirstName IS NULL)
        BEGIN
            UPDATE Person SET FirstName = prev.FirstName 
        END

Upvotes: 0

Views: 62

Answers (2)

Hart CO
Hart CO

Reputation: 34774

You can update a cte, so something like this should do it:

WITH CTE AS (
             SELECT
                   rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
                   p.FirstName
             FROM Person.Person p
             )
UPDATE a
SET a.Firstname = prev.Firstname
FROM CTE a
JOIN CTE prev ON prev.rownum = a.rownum - 1
WHERE a.FirstName IS NULL

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Your update is a separate statement from the select. If you want to do this in an update, try:

WITH CTE AS (
     SELECT rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
            p.FirstName
     FROM Person.Person p
)
UPDATE p
    SET FirstName = prev.FirstName 
    FROM CTE p JOIN
         CTE prev
         ON prev.rownum = CTE.rownum - 1
    WHERE CTE.FirstName IS NULL;

The left join isn't important here, because you are only changing rows where FirstName IS NULL. The left join would set the NULL value to itself.

Upvotes: 1

Related Questions