Advanece更新查询

Question

问题：

想要通过检查条件来更新Inflow，我们在reporting表的inflow列下面有什么值，例如，如果我们有一个案例WHEN flag = '1‘then 'AS’ELSE 'LI‘，它应该检查FLAG列的值，如果它是1，那么value应该是'AS’else 'L1‘注: reporting下的INFLOW列有不同的值。

表结构：

create table dummy ( ID VARCHAR(10), PRODUCT VARCHAR(10), INFLOW VARCHAR(10) ); 
INSERT INTO dummy(ID,PRODUCT) VALUES('10','999') 
INSERT INTO dummy(ID,PRODUCT) VALUES('11','888') 
INSERT INTO dummy(ID,PRODUCT) VALUES('12','111') 
INSERT INTO dummy(ID,PRODUCT) VALUES('13','222')

create table REPORTING_FLAG
(
ID VARCHAR(10),
PRODUCT VARCHAR(10),
INFLOW VARCHAR(2000),
FLAG VARCHAR(10),
L3 VARCHAR(10)
);

INSERT INTO REPORTING VALUES('10','999','CASE WHEN FLAG = ''1'' THEN ''AS'' ELSE ''LI''','1','SALR') 
INSERT INTO REPORTING VALUES('11','888','CASE WHEN L3 = ''SALR'' THEN ''SALR'' ELSE ''OTHR''','1','XYZ') 
INSERT INTO REPORTING VALUES('12','111','ABC','2','PQR') 
INSERT INTO REPORTING VALUES('13','222','SAP','3','QWE') 

Answer 1

在执行较大的插入时，明智且正确应用的逻辑是对数据集执行转换，而不是对每个单独的行执行转换。

即使这是不可避免的，也不要犯这样的错误，即习惯于大量的update/insert语句，这不仅会消耗资源，还会破坏统计信息，使索引和驱动器碎片化。

运行使用CASE逻辑的SELECT语句并验证结果。一旦您可以安全地对select语句执行此操作，请将其转换为Update语句(请注意，您的更新可以使用连接)

Answer 2

这应该是可行的

declare dummyproducts cursor for select ID, Product, Inflow from reporting
declare @ID VARCHAR(10), @PRODUCT VARCHAR(10), @INFLOW VARCHAR(2000)
OPEN dummyproducts
Fetch next from dummyProducts into @ID, @PRODUCT, @INFLOW
while @@fetch_Status = 0
BEGIN
    declare @sql nvarchar(max)
    if CHARINDEX('CASE', @INFLOW) > 0
        set @Inflow = @inflow + ' end'
    else
        set @inflow = 'r.inflow'
    set @sql = 'Update dummy set INFLOW = ' + 
                    @INFLOW  
                + ' from
                reporting r inner join dummy d on r.Product = d.Product and r.ID = d.ID where d.ID =  ' + @ID + ' and d.Product = ' + @Product
    print @sql
    exec (@sql)
    Fetch next from dummyProducts into @ID, @PRODUCT, @INFLOW
END
close dummyProducts
deallocate dummyProducts
GO