title: Update table without using cursor and on date
body: <p>Please copy and run following script</p>
  
  <pre><code>DECLARE @Customers TABLE (CustomerId INT)
  DECLARE @Orders TABLE ( OrderId INT, CustomerId INT, OrderDate DATETIME )
  DECLARE @Calls TABLE (CallId INT, CallTime DATETIME, CallToId INT, OrderId INT)
  -----------------------------------------------------------------
  INSERT INTO @Customers SELECT 1
  INSERT INTO @Customers SELECT 2
  INSERT INTO @Customers SELECT 3
  -----------------------------------------------------------------
  INSERT INTO @Orders SELECT 10, 1, DATEADD(d, -20, GETDATE())
  INSERT INTO @Orders SELECT 11, 1, DATEADD(d, -10, GETDATE())
  INSERT INTO @Orders SELECT 12, 2, DATEADD(d, -8, GETDATE())
  INSERT INTO @Orders SELECT 13, 2, DATEADD(d, -6, GETDATE())
  INSERT INTO @Orders SELECT 14, 3, DATEADD(d, -4, GETDATE())
  -----------------------------------------------------------------
  INSERT INTO @Calls SELECT 101, DATEADD(d, -19, GETDATE()), 1, NULL
  INSERT INTO @Calls SELECT 102, DATEADD(d, -17, GETDATE()), 1, NULL
  INSERT INTO @Calls SELECT 103, DATEADD(d, -9, GETDATE()), 1, NULL
  INSERT INTO @Calls SELECT 104, DATEADD(d, -6, GETDATE()), 1, NULL
  INSERT INTO @Calls SELECT 105, DATEADD(d, -5, GETDATE()), 1, NULL
  INSERT INTO @Calls SELECT 106, DATEADD(d, -4, GETDATE()), 2, NULL
  INSERT INTO @Calls SELECT 107, DATEADD(d, -2, GETDATE()), 2, NULL
  INSERT INTO @Calls SELECT 108, DATEADD(d, -2, GETDATE()), 3, NULL
  </code></pre>
  
  <p>I want to update @Calls table and need following results.</p>
  
  <p><img src="https://i.stack.imgur.com/RwVeV.png" alt="alt text"></p>
  
  <p>I am using the following query (Old query before answer)</p>
  
  <pre><code>UPDATE  @Calls
  SET     OrderId = ( 
                      CASE 
                          WHEN (s.CallTime &gt; e.OrderDate)
                            THEN e.OrderId
                      END                                 
                  )
  FROM    @Calls s INNER JOIN @Orders e   ON s.CallToId = e.CustomerId
  </code></pre>
  
  <p><strong>Edit:</strong>
  Now I am using this query</p>
  
  <pre><code>UPDATE c set OrderID = o1.OrderID
  from @Calls c inner join @Orders o1 on c.CallTime &gt; o1.OrderDate  
      left join @Orders o2 on c.CallTime &gt; o2.OrderDate 
              and o2.OrderDate &gt; o1.OrderDate 
  where o2.OrderID is null
  and o1.CustomerId = c.CallToId
  </code></pre>
  
  <p>and the result of my query is not what I need.</p>
  
  <p>Requirement:
  As you can see there are two orders. One is on <code>2010-12-12</code> and one is on <code>2010-12-22</code>. I want to update <code>@Calls</code> table with relevant OrderId with respect to CallTime.</p>
  
  <p><strong>In short If subsequent Orders are added, and there are further calls then we assume that a new call is associated with the most recent Order</strong></p>
  
  <p><strong>Note:</strong>
  This is sample data so this is not the case that I always have two Orders. There might be 10+ Orders and 100+ calls and 1000s of Customers.</p>
  
  <p><strong>Note2</strong>
  I could not find good title for this question. Please change it if you think of any better.</p>
  
  <p><strong>Edit2:</strong>
  The query provided in answer is taking too much time. Total number of records to update is around 250000.</p>
  
  <p>Thanks.</p>