CODEWITHSUNDEEP
sqlsql-serversql-server-2012cursors
user3809759
user3809759

Reputation: 23

Updating table records using cursors

I have the below assignments at work and wonder if anyone can help me out. I need to use cursors in order to update records based on next records. Whenever the key is not equal to 8, i need to check all the records after it where key starts with 8 and update date2 with its max date

My data looks like this:

╔══════════════════════════════════════╗
║     ID  date1      date2         key ║
╠══════════════════════════════════════╣
║     1   1/2/2014    5/2/2014      1  ║
║     1   5/2/2014    8/2/2014      8  ║
║     1   8/2/2014    9/2/2014      8  ║
║     1   11/2/2014   12/2/2014     1  ║   
║     1   12/2/2014   14/2/2014     8  ║ 
║     2   12/2/2014   14/2/2014     1  ║
║     2   14/2/2014   17/2/2014     8  ║
║     3   20/2/2014   23/2/2014     1  ║
╚══════════════════════════════════════╝

After being updated, data will look like:

╔══════════════════════════════════════╗
║     ID  date1      date2         key ║
╠══════════════════════════════════════╣
║     1   1/2/2014    9/2/2014      1  ║
║     1   5/2/2014    8/2/2014      8  ║
║     1   8/2/2014    9/2/2014      8  ║
║     1   11/2/2014   14/2/2014     1  ║   
║     1   12/2/2014   14/2/2014     8  ║ 
║     2   12/2/2014   17/2/2014     1  ║
║     2   14/2/2014   17/2/2014     8  ║
║     3   20/2/2014   23/2/2014     1  ║
╚══════════════════════════════════════╝

Upvotes: 0

Views: 109

Answers (2)

Andriy M
Andriy M

Reputation: 77737

If there was one key 1 row per ID and all existing key 8 rows with the same ID value were related to it, you could try this method:

WITH maxdates AS (
  SELECT
    *,
    maxdate2 = MAX(CASE [key] WHEN 8 THEN date2 END) OVER (PARTITION BY ID)
  FROM dbo.atable
)
UPDATE maxdates
SET date2 = maxdate2
WHERE [key] = 1
  AND maxdate2 IS NOT NULL
;

This is how it works. The maxdates common table expression uses a window MAX function to determine the maximum date2 values per group (per ID in this case). If this was the original dataset:

╔═══════════════════════════════╗
║ ID  date1      date2      key ║
╠═══════════════════════════════╣
║ 1   1/2/2014   5/2/2014   1   ║
║ 1   5/2/2014   8/2/2014   8   ║
║ 1   8/2/2014   9/2/2014   8   ║
║ 2   12/2/2014  14/2/2014  1   ║
║ 2   14/2/2014  17/2/2014  8   ║
║ 3   20/2/2014  23/2/2014  1   ║
╚═══════════════════════════════╝

the CTE would turn it into the following:

╔══════════════════════════════════════════╗
║ ID  date1      date2      key  maxdate2  ║
╠══════════════════════════════════════════╣
║ 1   1/2/2014   5/2/2014   1    9/2/2014  ║
║ 1   5/2/2014   8/2/2014   8    9/2/2014  ║
║ 1   8/2/2014   9/2/2014   8    9/2/2014  ║
║ 2   12/2/2014  14/2/2014  1    17/2/2014 ║
║ 2   14/2/2014  17/2/2014  8    17/2/2014 ║
║ 3   20/2/2014  23/2/2014  1    NULL      ║
╚══════════════════════════════════════════╝

The UPDATE statement would first filter out rows that are not to be updated, i.e. those where key is 8 as well as the key 1 rows that have no associated key 8 rows (determining them by the absent maxdate2), resulting in this subset:

╔══════════════════════════════════════════╗
║ ID  date1      date2      key  maxdate2  ║
╠══════════════════════════════════════════╣
║ 1   1/2/2014   5/2/2014   1    9/2/2014  ║
║ 2   12/2/2014  14/2/2014  1    17/2/2014 ║
╚══════════════════════════════════════════╝

and then update date2 with maxdate2.

Now, this method could still be applicable even if multiple key 1 rows per ID were allowed. You would simply need to come up with another criterion to recognise subgroups of relevant rows within the same ID group. That is, you would first need to turn a dataset like this:

╔═══════════════════════════════╗
║ ID  date1      date2      key ║
╠═══════════════════════════════╣
║ 1   1/2/2014   5/2/2014   1   ║
║ 1   5/2/2014   8/2/2014   8   ║
║ 1   8/2/2014   9/2/2014   8   ║
║ 1   12/2/2014  14/2/2014  1   ║
║ 1   14/2/2014  17/2/2014  8   ║
║ 1   20/2/2014  23/2/2014  1   ║
╚═══════════════════════════════╝

into something like this:

╔═════════════════════════════════════════╗
║ ID  date1      date2      key  rangeID  ║
╠═════════════════════════════════════════╣
║ 1   1/2/2014   5/2/2014   1    1        ║
║ 1   5/2/2014   8/2/2014   8    1        ║
║ 1   8/2/2014   9/2/2014   8    1        ║
║ 1   12/2/2014  14/2/2014  1    2        ║
║ 1   14/2/2014  17/2/2014  8    2        ║
║ 1   20/2/2014  23/2/2014  1    3        ║
╚═════════════════════════════════════════╝

then apply the method.

One way to add such a criterion would be to use a conditional running count, as in this query:

WITH partitions AS (
  SELECT
    *,
    rangeID = COUNT(CASE [key] WHEN 1 THEN 1 END) OVER (PARTITION BY ID ORDER BY date1)
  FROM dbo.atable
),
maxdates AS (
  SELECT
    *,
    maxdate2 = MAX(CASE [key] WHEN 8 THEN date2 END) OVER (PARTITION BY ID, rangeID)
  FROM partitions
)
UPDATE maxdates
SET date2 = maxdate2
WHERE [key] = 1
  AND maxdate2 IS NOT NULL
;

Basically, COUNT() OVER (... ORDER BY ...) is a running count and the CASE expression makes it conditional: the count only increases on key 1 rows and remains the same on the others. The partitions CTE obtains independent running count for every ID partition. As a result, you get rangeID values as illustrated previously.

The maxdates CTE reads the result of partitions and uses the rangeID values as the additional criterion I was talking about. The rest of the second query follows the first query's logic.

A live demonstration of this method can be found at SQL Fiddle.

Related manual pages that might be helpful:

Upvotes: 2

MarkD
MarkD

Reputation: 5316

You should not need a CURSOR.

Are you trying to do the following?

(NB - I've arranged the dates as dd/mm/yyyy, this is how it is done on other planets)

DECLARE @MyData TABLE (ID INT, date1 DATE, date2 DATE, [key] INT)
INSERT INTO @MyData (ID, date1, date2, [key])
    SELECT 1, '2/1/2014',    '2/5/2014',  1 UNION ALL
    SELECT 1, '2/5/2014',    '2/8/2014',  8 UNION ALL
    SELECT 1, '2/8/2014',    '2/9/2014',  8 UNION ALL
    SELECT 2, '2/12/2014',   '2/14/2014', 1 UNION ALL
    SELECT 2, '2/14/2014',   '2/17/2014', 8 UNION ALL
    SELECT 3, '2/20/2014',   '2/23/2014', 1

UPDATE MD
SET MD.date2 = DT.MaxDate2
FROM @MyData    MD
JOIN 
(
    SELECT   ID
            ,MaxDate2   = MAX(Date2)
    FROM @MyData
    WHERE [key] = 8
    GROUP BY ID
)           DT  ON DT.ID = MD.ID
WHERE MD.[key] != 8

SELECT *
FROM @MyData

Upvotes: 0

Related Questions