UPDATE statement help

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  November 20, 2015 at 8:29 am

  #1841379

  Just thought I'd toss in a CLUSTERED INDEX on the data, and see what the following looked like on execution plan:

  --===== If it exists, drop the test table to make reruns easier in SSMS.

  -- This is NOT a part of the solution.

  IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

  BEGIN

  DROP TABLE #TestTable;

  END

  --===== Create the test table. This is NOT a part of the solution.

  -- We're just creating test data here.

  SELECT td.*

  INTO #TestTable

  FROM (

  SELECT '3/31/2015',1,'Demo' UNION ALL

  SELECT '4/30/2015',1,'Demo' UNION ALL

  SELECT '5/31/2015',1,'New' UNION ALL

  SELECT '3/31/2015',2,'Wrong' UNION ALL

  SELECT '4/30/2015',2,'Wrong' UNION ALL

  SELECT '5/31/2015',2,'Correct'

  ) AS td (asOfDate, identifier, class4);

  CREATE UNIQUE CLUSTERED INDEX IX_TestTable_identifier_asOfDate ON #TestTable

  (

  identifier ASC,

  asOfDate DESC

  );

  --===== Show the "BEFORE" content of the table.

  SELECT *

  FROM #TestTable;

  -- DO THE UPDATE

  WITH MAX_VALUES AS (

  SELECT TT.identifier, MAX(TT.asOfDate) AS MAX_DATE

  FROM #TestTable AS TT

  GROUP BY TT.identifier

  )

  UPDATE T

  SET T.class4 = T2.class4

  FROM #TestTable AS T

  INNER JOIN MAX_VALUES AS MV

  ON T.identifier = MV.identifier

  INNER JOIN #TestTable AS T2

  ON MV.MAX_DATE = T2.asOfDate

  AND MV.identifier = T2.identifier

  WHERE T.asOfDate <> T2.asOfDate;

  --===== Show the "AFTER" content of the table.

  SELECT *

  FROM #TestTable;

  I get 2 clustered index seeks, a clustered index scan, a stream aggregate, two nested loops, a top, an eager spool (Table Spool), and a clustered index update on just the 4 rows that need it. As I don't have a 2012 instance to test with, I couldn't use anything but 2008 R2 code. Let me know how well that compares.... I'll look at it as a learning exercise.

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)

• lnardozi 61862

  SSCommitted

  Points: 1931

  More actions

  November 20, 2015 at 11:07 am

  #1841419

  Gotta stick my 2 cents in - how about this?

  update #testTable set class4 = d.class4 from

  #testTable a inner join

  ( select identifier,class4 from

  (select identifier,max(asofDate) asOfDate from #testTable group by identifier ) b

  outer apply (select class4 from #TestTable c where c.identifier = b.identifier and c.asOfDate = b.asofdate) c) d on d.identifier = a.identifier

  select * from #testtable