Why would updating statistics change a result set

TomI 2015-05-15 18:30:39

I have a procedure that returns a different result after update statistics. I can understand a plan change, but I don't understand the result set change. I am attaching two plans, one with the plan for the desired result, and one with the plan for a result set that includes NULLs.

The procedure inserts a row in a temp table, then uses an update which uses PIVOT as part of a join. The desired result is that the 3 values from the PIVOT are used to update the temp table. In the NULL result, only 1 of the 3 values from the PIVOT are updated in the temp table

The procedure call is exec usp_SEL_HES_EQUIP_RECOMM_DETAILS 867182,'Refrigerator';

Here is the code to create the tables:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EQUIP_INV_DETL](
    [EQUIP_INV_DETL_ID] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
    [USER_ID] [varchar](30) NOT NULL,
    [XACTN_TS] [datetime] NOT NULL,
    [EQUIP_INV_ID] [numeric](9, 0) NULL,
    [HES_RCMNDN_ID] [numeric](9, 0) NULL,
    [EQUIP_INV_DETL_TYPE_ID] [numeric](9, 0) NOT NULL,
    [REF_TXT] [varchar](100) NOT NULL,
 CONSTRAINT [EQUIP_INV_DETL_PK] PRIMARY KEY CLUSTERED 
(
    [EQUIP_INV_DETL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
CREATE TABLE [dbo].[EQUIP_INV_DETL_TYPE](
    [EQUIP_INV_DETL_TYPE_ID] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
    [USER_ID] [varchar](30) NOT NULL,
    [XACTN_TS] [datetime] NOT NULL,
    [EQUIP_INV_DETL_TYPE_NM] [varchar](30) NOT NULL,
    [EQUIP_INV_DETL_TYPE_DSC] [varchar](200) NULL,
    [ACTV_IND] [char](1) NOT NULL,
 CONSTRAINT [EQUIP_INV_DETL_TYPE_PK] PRIMARY KEY CLUSTERED 
(
    [EQUIP_INV_DETL_TYPE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
CREATE TABLE [dbo].[HES_RCMNDN](
    [HES_RCMNDN_ID] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
    [CLM_PROJ_ID] [numeric](9, 0) NOT NULL,
    [IMPRVMT_TYPE_NM] [varchar](20) NOT NULL,
    [USER_ID] [varchar](30) NOT NULL,
    [XACTN_TS] [datetime] NOT NULL,
    [MEAS_TXT] [varchar](50) NULL,
    [UM_CD] [varchar](5) NULL,
    [UNIT_TYPE_CD] [varchar](2) NULL,
    [HES_RCMNDN_DSC] [varchar](200) NULL,
 CONSTRAINT [HES_RCMNDN_PK] PRIMARY KEY CLUSTERED 
(
    [HES_RCMNDN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
 
CREATE NONCLUSTERED INDEX [EQUIP_INV_DETL_IE1] ON [dbo].[EQUIP_INV_DETL]
(
    [HES_RCMNDN_ID] ASC
)
INCLUDE (   [EQUIP_INV_DETL_ID],
    [EQUIP_INV_DETL_TYPE_ID],
    [REF_TXT]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX [HES_RCMNDN_IE1] ON [dbo].[HES_RCMNDN]
(
    [CLM_PROJ_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Here is the code to load a subset of the data needed to meet the parameters of the procedure call

-- 
-- TABLE INSERT STATEMENTS
-- 
SET IDENTITY_INSERT dbo.HES_RCMNDN ON
GO
INSERT INTO dbo.HES_RCMNDN ( HES_RCMNDN_ID, CLM_PROJ_ID, IMPRVMT_TYPE_NM, USER_ID, XACTN_TS, MEAS_TXT, UM_CD, UNIT_TYPE_CD, HES_RCMNDN_DSC ) 
         VALUES ( 371533, 867182, N'Refrigerator', N'ABC', N'01/26/2015 03:27:08.740 PM', NULL, NULL, NULL, NULL ) 
go
SET IDENTITY_INSERT dbo.HES_RCMNDN OFF
GO
SET IDENTITY_INSERT dbo.EQUIP_INV_DETL_TYPE ON
GO
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 1, N'abc', N'02/02/2012 03:43:50.197 PM', N'Manufacturer Name', N'Manufacturer of Existing/Recommended Appliance', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 2, N'abc', N'02/02/2012 03:43:50.197 PM', N'Model Number', N'Model of Existing/Recommended Appliance', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 3, N'abc', N'02/02/2012 03:43:50.197 PM', N'Age', N'Age of Existing Appliance', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 4, N'abc', N'02/02/2012 03:43:50.197 PM', N'Type', N'Type of Existing/Recommended Appliance', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 5, N'abc', N'02/02/2012 03:43:50.197 PM', N'Volume', N'Volume of Existing/Recommended Appliance', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 6, N'abc', N'02/02/2012 03:43:50.197 PM', N'Fresh Volume', N'Fresh Volume of Existing/Recommended Refrigerator', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 7, N'abc', N'02/02/2012 03:43:50.197 PM', N'Freezer Volume', N'Freezer Volume of Existing/Recommended Refrigerator', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 8, N'abc', N'02/02/2012 03:43:50.197 PM', N'Consumption in kWh', N'Consumption of Existing/Recommended Appliance in kilo Watt hour', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 9, N'abc', N'02/02/2012 03:43:50.197 PM', N'Estimated Cost', N'Estimated Cost of a Recommended Appliance', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 10, N'abc', N'02/02/2012 03:43:50.197 PM', N'Is Upgrade Recommended', N'Is an Upgrade recommended for existing Appliance', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 11, N'abc', N'02/02/2012 03:43:50.197 PM', N'Is Rebate Form Given', N'Is Rebate Form Given to the Customer', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 12, N'abc', N'02/02/2012 03:43:50.197 PM', N'Reason For Not Leaving Rebate', N'Reason for not leaving a rebate form', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 13, N'abc', N'02/02/2012 03:43:50.197 PM', N'Dryer Manufacturer', N'Manufacturer Existing Clothes Dryer', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 14, N'abc', N'02/02/2012 03:43:50.197 PM', N'Dryer Model', N'Model of Existing Clothes Dryer', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 15, N'abc', N'02/02/2012 03:43:50.197 PM', N'Dryer Age', N'Age of Existing Clothes Dryer', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 16, N'abc', N'02/02/2012 03:43:50.197 PM', N'Fuel', N'Existing/Recommended Resource Fuel', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 17, N'abc', N'02/02/2012 03:43:50.197 PM', N'Pints/Day', N'Existing/Recommended Dehumidifier Pints/Day', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 18, N'abc', N'02/02/2012 03:43:50.197 PM', N'Energy Fact.', N'Existing/Recommended Dehumidifier Engery Fact.', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 19, N'abc', N'02/02/2012 03:43:50.197 PM', N'Humidity', N'Humidity for existing Dehumidifier', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 20, N'abc', N'02/02/2012 03:43:50.197 PM', N'Ton', N'Existing/Recommended Cooling Ton', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 21, N'abc', N'02/02/2012 03:43:50.197 PM', N'EER', N'Existing/Recommended Cooling EER', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 22, N'abc', N'02/02/2012 03:43:50.197 PM', N'SEER', N'Existing/Recommended Cooling SEER', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 23, N'abc', N'02/02/2012 03:43:50.197 PM', N'Unit Location', N'Location for any Appliance or Measure', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 24, N'abc', N'02/02/2012 03:43:50.197 PM', N'Dimensions', N'Dimensions (WxLxH) of Existing Room AC', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 25, N'abc', N'02/02/2012 03:43:50.197 PM', N'BTUH', N'Existing/Recommended BTUH', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 26, N'abc', N'02/02/2012 03:43:50.197 PM', N'AFUE', N'AFUE of Existing Heating System', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 27, N'abc', N'02/02/2012 03:43:50.197 PM', N'COP', N'Existing/Recommended COP of Heating System', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 28, N'abc', N'02/02/2012 03:43:50.197 PM', N'HSPF', N'Existing/Recommended HSPF of Heating System', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 29, N'abc', N'02/02/2012 03:43:50.197 PM', N'Area Type', N'Area Type of Existing Insulation', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 30, N'abc', N'02/02/2012 03:43:50.197 PM', N'RVal', N'Existing/Recommended R-Val of Insulation', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 31, N'abc', N'02/02/2012 03:43:50.197 PM', N'Square Feet', N'Square Feet of Existing Insulation', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 32, N'abc', N'02/02/2012 03:43:50.197 PM', N'Size Length', N'Length of Existing Window in Inches', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 33, N'abc', N'02/02/2012 03:43:50.197 PM', N'Size Width', N'Width of Existing Window in Inches', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 34, N'abc', N'02/02/2012 03:43:50.197 PM', N'Quantity', N'Qunatity of any Appliance or Measure', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 35, N'abc', N'02/02/2012 03:43:50.197 PM', N'Lighting Bulb Wattage', N'Wattage of a Lighting Bulb', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 36, N'abc', N'02/02/2012 03:43:50.197 PM', N'CFM Reduction', N'CFM Reduction for Recommended Duct Sealing', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 37, N'abc', N'02/02/2012 03:43:50.197 PM', N'Pressure', N'Pressure Quantity for Recommended Duct Sealing', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 38, N'abc', N'02/02/2012 03:43:50.197 PM', N'Comments', N'Additional Comments for any Appliance or Measure', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 39, N'abc', N'02/02/2012 03:43:50.197 PM', N'Estimated Wall Cost', N'Estimated Wall Cost for an Insulation', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 40, N'abc', N'02/02/2012 03:43:50.197 PM', N'Estimated Ceiling Cost', N'Estimated Ceiling Cost for an Insulation', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 41, N'abc', N'02/02/2012 03:43:50.197 PM', N'Rsn For Not Recomnding Upgrade', N'Reason for not recommending an upgrade (For Insulation and Windows)', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 42, N'abc', N'02/02/2012 03:43:50.197 PM', N'Bulb Information', N'Lighting Bulb Information', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 43, N'abc', N'04/05/2012 03:17:31.633 PM', N'Attic Hatch Recommended?', N'Is Attic Hatch recommended', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 44, N'abc', N'04/05/2012 03:17:31.633 PM', N'Attic Stair Recommended?', N'Is Attic Stair recommended', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 45, N'abc', N'04/05/2012 03:17:31.633 PM', N'Whole House Fan Recommended?', N'Is Whole House Fan recommended', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 46, N'abc', N'04/05/2012 03:17:31.633 PM', N'Door Kit Quantity', N'Door Kit Quantity recommended', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 47, N'abc', N'04/05/2012 03:17:31.633 PM', N'Door Sweep Quantity', N'Door Sweep Quantity recommended', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 48, N'abc', N'04/05/2012 03:17:31.633 PM', N'Caulking & Sealing in Feet', N'Caulking & Sealing in Feet recommended', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 49, N'abc', N'04/05/2012 03:17:31.633 PM', N'Outlet/Switch Gaskets Quantity', N'Outlet/Switch Gaskets Quantity recommended', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 50, N'abc', N'04/05/2012 03:17:31.633 PM', N'Weatherstrip Door Quantity', N'Weatherstrip Door Quantity recommended', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 51, N'abc', N'04/05/2012 03:17:31.633 PM', N'Weatherstrip Window Quantity', N'Weatherstrip Window Quantity recommended', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 52, N'abc', N'04/05/2012 03:17:31.633 PM', N'Sealing Service (Hours)', N'Sealing Service (Hours) recommended', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 53, N'abc', N'04/26/2012 03:18:38.470 PM', N'Axis Orientation', N'Axis Orientation for a Clothes Washer', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 54, N'abc', N'02/14/2013 03:21:04.203 PM', N'MEF', N'MEF of recommended Clothes washer', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 55, N'abc', N'02/14/2013 03:21:04.203 PM', N'WF', N'WF of recommended Clothes washer', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 56, N'abc', N'02/27/2014 03:07:51.060 PM', N'Estimated Basement Cost', N'Estimated Basement Cost for an Insulation', N'Y' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL_TYPE ( EQUIP_INV_DETL_TYPE_ID, USER_ID, XACTN_TS, EQUIP_INV_DETL_TYPE_NM, EQUIP_INV_DETL_TYPE_DSC, ACTV_IND ) 
         VALUES ( 57, N'abc', N'02/27/2014 03:08:52.740 PM', N'GroundFactor', N'Ground Factor for Insulation', N'Y' ) 
go
SET IDENTITY_INSERT dbo.EQUIP_INV_DETL_TYPE OFF
GO
SET IDENTITY_INSERT dbo.EQUIP_INV_DETL ON
GO
INSERT INTO dbo.EQUIP_INV_DETL ( EQUIP_INV_DETL_ID, USER_ID, XACTN_TS, EQUIP_INV_ID, HES_RCMNDN_ID, EQUIP_INV_DETL_TYPE_ID, REF_TXT ) 
         VALUES ( 2512280, N'ABC', N'01/26/2015 03:27:08.747 PM', NULL, 371533, 12, N'Recent Model (Unit Age)' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL ( EQUIP_INV_DETL_ID, USER_ID, XACTN_TS, EQUIP_INV_ID, HES_RCMNDN_ID, EQUIP_INV_DETL_TYPE_ID, REF_TXT ) 
         VALUES ( 2512281, N'ABC', N'01/26/2015 03:27:08.747 PM', NULL, 371533, 8, N'0' ) 
go
INSERT INTO dbo.EQUIP_INV_DETL ( EQUIP_INV_DETL_ID, USER_ID, XACTN_TS, EQUIP_INV_ID, HES_RCMNDN_ID, EQUIP_INV_DETL_TYPE_ID, REF_TXT ) 
         VALUES ( 2512282, N'ABC', N'01/26/2015 03:27:08.747 PM', NULL, 371533, 11, N'No' ) 
go
SET IDENTITY_INSERT dbo.EQUIP_INV_DETL OFF
GO

Here is the desired result set

Clm_Proj_Id HES_RCMNDN_ID   Equip_Inv_Detl_Id   IsRebateFormGiven   Type    Volume  FreshVolume FreezerVolume   ConsumptionInKwh    EstimatedCost   ReasonForNotLeavingRebate   IMPRVMT_TYPE_NM DryerFuel   MEF WF  PintsPerDay EnergyFact  SeasonalEnergyEfficiencyRatio   BritishThermalUnitPerHour   EnergyEfficiencyRatio   CoefficientOfPerformance    HeatingSeasonalPerformanceFactor    AnnualFuelUtilizationEfficiency RVal    EstimatedCeilingCost    EstimatedWallCost   EstimatedBasementCost
867182  371533  2512280 No  NULL    NULL    NULL    NULL    0   NULL    Recent Model (Unit Age) Refrigerator    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

Here is the 'NULL' result set

Clm_Proj_Id HES_RCMNDN_ID   Equip_Inv_Detl_Id   IsRebateFormGiven   Type    Volume  FreshVolume FreezerVolume   ConsumptionInKwh    EstimatedCost   ReasonForNotLeavingRebate   IMPRVMT_TYPE_NM DryerFuel   MEF WF  PintsPerDay EnergyFact  SeasonalEnergyEfficiencyRatio   BritishThermalUnitPerHour   EnergyEfficiencyRatio   CoefficientOfPerformance    HeatingSeasonalPerformanceFactor    AnnualFuelUtilizationEfficiency RVal    EstimatedCeilingCost    EstimatedWallCost   EstimatedBasementCost
867182  371533  2512280 NULL    NULL    NULL    NULL    NULL    NULL    NULL    Recent Model (Unit Age) Refrigerator    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

The more I look at this, the more I think that the 'NULL' result is the correct one.

Aaron Bertrand 2015-05-15 19:01:43
Updating the stats marked all related queries for recompilation, and it so happened that when you recompiled, you got a different plan. The biggest differences I see are – on the NULL result – a stream aggregate is present, and a sort operation has become distinct. These are both likely choices made based on different cardinality estimates.

Both operations seemed to ultimately update exactly one row. It is tough for us to reproduce this issue and see what you mean by "1 of the 3 values" since we don't know the source data or what the PIVOT output looked like before the UPDATE. If you can provide a repro with sample data and desired results, it would make it easier to track down the issue.

In the meantime, I would recommend this syntax instead (the difference is subtle):

 UPDATE EID
  SET IsRebateFormGiven=PIV.[Is Rebate Form Given],
  ...
  FROM #RECOMM_DETL AS EID
  INNER JOIN 
  (
    SELECT ...
    FROM 
    (
      SELECT ...
      FROM dbo.HES_RCMNDN AS HD 
      LEFT JOIN dbo.EQUIP_INV_DETL AS EID 
        ON HD.HES_RCMNDN_ID = EID.HES_RCMNDN_ID
      LEFT JOIN dbo.EQUIP_INV_DETL_TYPE AS EIDT 
        ON EID.EQUIP_INV_DETL_TYPE_ID = EIDT.EQUIP_INV_DETL_TYPE_ID    
      WHERE HD.CLM_PROJ_ID = @CLM_PROJ_ID 
        AND HD.IMPRVMT_TYPE_NM = @IMPRVMT_TYPE_NM 
    ) AS P
    PIVOT 
    (
      MIN(REF_TXT)
      FOR EQUIP_INV_DETL_TYPE_NM IN (...)
       -- note that because EIDT is left joined,
       -- the above could be NULL - should you
       -- consider an INNER JOIN inside P?
    ) AS PVT
  ) AS PIV 
  ON PIV.CLM_PROJ_ID = EID.CLM_PROJ_ID 
  AND PIV.HES_RCMNDN_ID = EID.HES_RCMNDN_ID;