如何批量更新？

Question

我编写了以下查询来选择多个记录：

select      
    b.building_name , 
    lp.lease_id , 
    lp.suite_id , 
    lp.suite_name , 
    lp.tenant_trading_name ,  
    sco.scca_uplift as 'Current GST Uplift Jan 2011' , 
    nu.new_uplift as 'GST Uplift Jan 2011 Post-Change' ,
    lp.scca_broad_category_code , 
    lp.scca_sales_group_code , 
    lp.scca_fine_categories_code , 
    lp.scca_notes_code
    , '(lease_id = ''' + lp.lease_id  + ''' and suite_id =''' 
        + lp.suite_id + ''') or' as 'Base SQL Output'
from        
    property.lease_period lp
        inner join  property.property b
            on  b.building_id = lp.building_id
        inner join
        (
            select  scca_uplift ,
                    scca_broad_category_code ,
                    scca_fine_categories_code ,
                    scca_sales_group_code
            from    property.lease_period
            where   period_id = 252
        ) sco
            on  sco.scca_broad_category_code = lp.scca_broad_category_code
                and sco.scca_sales_group_code = lp.scca_sales_group_code
                and sco.scca_fine_categories_code = lp.scca_fine_categories_code

        inner join
        (
            -- Department Store
            select '10' as scca_broad_category_code, 
            '100'       as scca_sales_group_code,
            '1000'      as scca_fine_categories_code,
            109.8       as new_uplift 
            -- Discount Department Stores
            union all
            select '10' as scca_broad_category_code, 
            '110'       as scca_sales_group_code,
            '1100'      as scca_fine_categories_code,
            109.5       as new_uplift
            -- Supermarket
            union all
            select '10' as scca_broad_category_code, 
            '130'       as scca_sales_group_code,
            '1300'      as scca_fine_categories_code,
            105.0       as new_uplift
            -- Newsagents and Stationary
            union all
            select '30' as scca_broad_category_code, 
            '340'       as scca_sales_group_code,
            '3403'      as scca_fine_categories_code,
            110.0       as new_uplift
            -- Discount Variety
            union all
            select '30' as scca_broad_category_code, 
            '350'       as scca_sales_group_code,
            '3502'      as scca_fine_categories_code,
            109.5       as new_uplift
            -- Optometrist
            union all
            select '30' as scca_broad_category_code, 
            '380'       as scca_sales_group_code,
            '3801'      as scca_fine_categories_code,
            110.0       as new_uplift

        ) nu
            on      nu.scca_broad_category_code = lp.scca_broad_category_code
                    and nu.scca_sales_group_code = lp.scca_sales_group_code
                    and nu.scca_fine_categories_code = lp.scca_fine_categories_code
where   
    (
        -- Department Store
        (   lp.scca_broad_category_code = '10' and
            lp.scca_sales_group_code = '100' and
            lp.scca_fine_categories_code = '1000' and
            lp.scca_notes_code in ('10001', '10002'))
        -- Discount Department Store
        or
        (   lp.scca_broad_category_code = '10' and
            lp.scca_sales_group_code = '110' and
            lp.scca_fine_categories_code = '1100' and   
            lp.scca_notes_code in ('11001', '11002', '11003', '11004'))
        -- Supermarket
        or
        (   lp.scca_broad_category_code = '10' and
            lp.scca_sales_group_code = '130' and
            lp.scca_fine_categories_code = '1300' and   
            lp.scca_notes_code in ( '13001', '13002', '13003', '13004', '13005', 
                                    '13006', '13007', '13007', 
                                    '13008', '13009', '13010', '13011', '13012', 
                                    '13013', '13014'))
        -- Newsagents and Stationary
        or
        (   lp.scca_broad_category_code = '30' and
            lp.scca_sales_group_code = '340' and
            lp.scca_fine_categories_code = '3403')
        -- Discount Variety
        or
        (   lp.scca_broad_category_code = '30' and
            lp.scca_sales_group_code = '350' and
            lp.scca_fine_categories_code = '3502')
        -- Optometrist
        or
        (   lp.scca_broad_category_code = '30' and
            lp.scca_sales_group_code = '380' and
            lp.scca_fine_categories_code = '3801')

    )
    and     b.building_id <> 42000
    and     lp.scca_uplift is not null
    and     lp.period_id >= 252
order by    
    b.building_name , 
    scca_broad_category_code , 
    scca_sales_group_code , 
    scca_fine_categories_code , 
    scca_notes_code

我现在想要做的是编写一个查询，它将接受nu.new_uplift列，并将其应用于它当前显示的每一行和它现在显示的值(令人困惑的句子，请告诉我是否应该以不同的方式解释)。我认为我可以通过使用CTE来做到这一点，但我不认为这是可能的，因为我对我的连接使用子查询。记住这一点--我应该在这里利用什么？

Answer 1

从非常、非常宽泛的角度来看，UPDATE... from...应该可以在这里工作。一种可能的模板：

UPDATE TableName
 set ColName = Alias2.OtherColName
 from TableName Alias1
  inner join OtherTableName Alias2
   on JoinCriteria
 where FilteringCriteria

与SELECT语句一样，"OtherTableName“可以替换为几乎任何表oS子查询。

在这里，我将选择要更新的表，将其作为"from“子句中列出的第一个表，然后将其适当地连接到将成为怪物的子查询(不包括ORDER BY子句)。

Answer 2

我使用以下方法来应用此更改(出于兴趣而发布)，但是Philip的发布方法要好得多：

begin transaction

update  property.lease_period
set     scca_uplift =
                    (
                        case
                            when -- Department Store
                                (
                                    scca_notes_code in ('10001', '10002') and
                                    scca_broad_category_code = '10' and
                                    scca_sales_group_code = '100' and
                                    scca_fine_categories_code = '1000'
                                )
                            then    '109.8'
                            when -- Discount Department Store
                                (
                                    scca_notes_code in ('11001', '11002', '11003', '11004') and
                                    scca_broad_category_code = '10' and
                                    scca_sales_group_code = '110' and
                                    scca_fine_categories_code = '1100'
                                )
                            then    '109.5'
                            when -- Supermarket
                                (
                                    scca_notes_code in (    '13001', '13002', '13003', '13004', '13005', 
                                                            '13006', '13007', '13007', 
                                                            '13008', '13009', '13010', '13011', '13012', 
                                                            '13013', '13014') and
                                    scca_broad_category_code = '10' and
                                    scca_sales_group_code = '130' and
                                    scca_fine_categories_code = '1300'
                                )
                            then    '105.0'
                            when -- Newsagents and Stationary
                                (
                                    scca_broad_category_code = '30' and
                                    scca_sales_group_code = '340' and
                                    scca_fine_categories_code = '3403'
                                )
                            then    '110.0'
                            when -- Discount Variety
                                (
                                    scca_broad_category_code = '30' and
                                    scca_sales_group_code = '350' and
                                    scca_fine_categories_code = '3502'
                                )
                            then    '109.5'
                            when -- Optometrist
                                (
                                    scca_broad_category_code = '30' and
                                    scca_sales_group_code = '380' and
                                    scca_fine_categories_code = '3801'
                                )
                            then    '110.0'
                        end
                    )
where   
    period_id >= 252 and
    building_id <> 42000 and
    scca_uplift is not null

rollback transaction