Refresh table without using cursor and date

Question

Copy and run the following script

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 

I want to update the @Calls table and get the following results.

I use the following query (old query before replying)

 UPDATE @Calls SET OrderId = ( CASE WHEN (s.CallTime > e.OrderDate) THEN e.OrderId END ) FROM @Calls s INNER JOIN @Orders e ON s.CallToId = e.CustomerId 

Edit: Now I am using this query

 UPDATE c set OrderID = o1.OrderID from @Calls c inner join @Orders o1 on c.CallTime > o1.OrderDate left join @Orders o2 on c.CallTime > o2.OrderDate and o2.OrderDate > o1.OrderDate where o2.OrderID is null and o1.CustomerId = c.CallToId 

and the result of my query does not match me.

Requirement: As you can see, there are two orders. One is located on 2010-12-12 , and one is on 2010-12-22 . I want to update the @Calls table with the corresponding OrderId regarding CallTime.

In Brief If subsequent Orders are added and there are further calls, we assume that the new call is associated with the most recent Order

Note: This is an example of data, so this is not the case when I always have two orders. There may be 10+ Orders and 100+ calls and 1000 customers.

Note2 I could not find a good name for this question. Please change it if you think about it better.

Edit2: The request provided in response takes too much time. The total number of records for updating is about 250,000.

Thanks.

Answer 1

You can use the left join to check for "unwanted" strings and eliminate them in your WHERE :

 UPDATE c set OrderID = o1.OrderID from @Calls c inner join @Orders o1 on c.CallTime > o1.OrderDate left join @Orders o2 on c.CallTime > o2.OrderDate and o2.OrderDate > o1.OrderDate where o2.OrderID is null select * from @Calls 

those. first you set up your request using regular connections - you want to find the line in orders (o1), which occurs before the call (c). But this can return several lines (if there is more than one before the call (c). Thus, you perform another connection with orders (o2) that look for lines that occur after the line found in (o1), but before the call ( c) If we can make such a match, then we don’t want this string (o1) in the end, so we filter this combo string in the WHERE clause.

---

Now that you have added the CustomerID, you need to consider that in your conditions of connection, too - that the left connection with @Orders (o2) will look for any subsequent call, and not just one for the same client. To maintain symmetry between two compounds in order:

 UPDATE c set OrderID = o1.OrderID from @Calls c inner join @Orders o1 on c.CallTime > o1.OrderDate and c.CallToID = o1.CustomerId /* <-- New line 1 */ left join @Orders o2 on c.CallTime > o2.OrderDate and o2.OrderDate > o1.OrderDate and c.CallToID = o2.CustomerId /* <-- New line 2 */ where o2.OrderID is null 

It will also hopefully fix some performance issues.