Update using CTE - Syntax error?

Question

I'm trying to update a table using data contained in a CTE. Unfortunately I'm receiving a syntax error and I'm not quite sure why. The code currently is:

declare @period_id  integer =
                (
                     select period_id
                     from   property.period
                     where  getdate() between period_start and period_end
                 )

;with cte_reclassified as
(
    select  building_id ,
            lease_id ,
            scca_broad_category_code ,
            scca_fine_categories_code ,
            scca_notes_code ,
            scca_sales_group_code ,
            scca_uplift
    from    property.lease_period
    where   period_id = @period_id
)

update  property.lease_period lp
from    cte_reclassified r
set     lp.scca_broad_category_code = r.scca_broad_category_code
where   lp.lease_id = r.lease_id
        and lp.building_id = r.building_id

The syntax error I'm receiving is:

Msg 102, Level 15, State 1, Line 21 Incorrect syntax near 'lp'.

Is there a way to do what i'm trying to attempt here? I've tried googling the subject but hit dead ends - any advice would be appreciated!

You sure you pasted it all in exactly as it was? I wonder this because the letters "`lp`" don't appear anywhere in the code — Adam Batkin, Jul 30 '12 at 01:56
@AdamBatkin Whoops! Bi-product of cutting it down for s/oflow. Updating now. edit: Fixed! — Michael A, Jul 30 '12 at 01:57
I don't think SQL Server lets you specify an alias in an update statement. just remove the alias and replace the occurrences with the table name. — Gordon Linoff, Jul 30 '12 at 01:59

score 2 · Accepted Answer · answered Jul 30 '12 at 02:02

2

I think you want to take the "property" out of the UPDATE part of the statement (since you are updating through the CTE) and put the SET clause before the FROM:

update  lease_period lp
set     lp.scca_broad_category_code = r.scca_broad_category_code
from    cte_reclassified r
where lp.lease_id = r.lease_id

answered Jul 30 '12 at 02:02

Adam Batkin

51,711
9
123
115

And as @GordonLinoff mentioned, you may need to take the alias `lp` out of the statement – Adam Batkin Jul 30 '12 at 02:04

score 1 · Answer 2 · edited May 23 '17 at 12:03

1

You do not need to create alias on your update statement

On its syntax : Update [TableName] SET [ColumnName]='New Value' WHERE ColumnName='Filter'

have a look on this SO post on how it is done by @Robin Day:

SQL Server UPDATE from SELECT

Best Regards

edited May 23 '17 at 12:03

Community

1
1

answered Jul 30 '12 at 02:28

BizApps

6,048
9
40
62

Update using CTE - Syntax error?

2 Answers2