CODEWITHSUNDEEP
sqlsql-server
Vladimir
Vladimir

Reputation: 3

How to rewrite query

I need to update based on a select. The following errors with: the column '' was specified multiple times for Q

UPDATE Evolution1.DimAdministrator 
SET  Evolution1.DimAdministrator.ClaimSystemCodeId = 17 
FROM Evolution1.DimAdministrator da  INNER JOIN (
    Select
        ExtractId,   
        base.AdministratorId, 
        base.CardprocessorAdministratorId, 
        AdministratorName,                                 
        EffectiveDate,           
        CancelDate ,             
        State,
        StageError  ,
        AdministratorKey,
        CustomerKey ,
        Name            ,                                                                                     
        EffectiveDateKey ,
        CancelDateKey,
        StateProvinceKey ,
        Alias ,
        NavId     ,  
        warehouse.AdministratorId ,
        warehouse.CardprocessorAdministratorId,
        warehouse.ClaimSystemCodeId, 
        Inserted ,               
        Updated
    FROM OneStage.OnePay.Administrator base
    INNER JOIN OneWarehouse.Evolution1.DimAdministrator warehouse ON base.AdministratorId = warehouse.AdministratorId
    WHERE base.ClaimSystemCodeId <> warehouse.ClaimSystemCodeId
    AND base.ClaimSystemCodeId = 1
) AS Q

Help please. Thanks.

Upvotes: 0

Views: 39

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

You have multiple columns with duplicate names.

Put an alias on them like this:

UPDATE Evolution1.DimAdministrator 
SET  Evolution1.DimAdministrator.ClaimSystemCodeId = 17 
FROM Evolution1.DimAdministrator da  INNER JOIN (
    Select
        ExtractId,   
        base.AdministratorId AS base_AdminID, 
        base.CardprocessorAdministratorId AS base_CardID, 
        AdministratorName,                                 
        EffectiveDate,           
        CancelDate ,             
        State,
        StageError  ,
        AdministratorKey,
        CustomerKey ,
        Name            ,                                                                                     
        EffectiveDateKey ,
        CancelDateKey,
        StateProvinceKey ,
        Alias ,
        NavId     ,  
        warehouse.AdministratorId wh_AdminID,
        warehouse.CardprocessorAdministratorId AS WH_CardID,
        warehouse.ClaimSystemCodeId, 
        Inserted ,               
        Updated
    FROM OneStage.OnePay.Administrator base
    INNER JOIN OneWarehouse.Evolution1.DimAdministrator warehouse ON base.AdministratorId = warehouse.AdministratorId
    WHERE base.ClaimSystemCodeId <> warehouse.ClaimSystemCodeId
    AND base.ClaimSystemCodeId = 1
) AS Q

Are you sure you don't need to JOIN Q ON something?

Upvotes: 1

Related Questions