title: Tuning the update statement in SQL Server or upgrade the query in T-SQL like Oracle plsql (for idx update)
body: <p>Here is my update query in SQL Server, it is taking a long time to execute. I need help to re-write the query.</p>
  <pre><code>UPDATE d
  SET d.CA_PAID_TO_DATE = s.canextpdate
  FROM 
      (SELECT LTRIM(RTRIM(POLNUM)) POLNUM, CA_PAID_TO_DATE 
       FROM [DataAnalytics].[dbo].[tbla_hst_policy_history]  
       WHERE transmonth = '202112'
         AND CA_PAID_TO_DATE IS NULL) AS d
  INNER JOIN 
      (SELECT 
           LTRIM(RTRIM(CAPOLNUM)) CAPOLNUM, 
           CASE 
               WHEN LEFT(canextpdate, 2) &gt; 85 
                   THEN '19' + canextpdate 
                   ELSE '20' + canextpdate 
           END canextpdate  
       FROM [DataAnalytics].[dbo].[tblh_lldca] 
       WHERE transmonth = '202112' ) AS s ON (d.POLNUM = s.CAPOLNUM);
  </code></pre>