how to update multiple rows using list stored in table variables in SQL

Question

I'm currently trying to implement a bit of code that will take the dates for a given list of 160ish rows and update them to be one minute after another date from the same table.

so for example from these would go from:

> 1058841   2018-06-20 14:15:04.000 Copy of NtO produced
> 1058841   2018-06-14 19:58:03.000 NTO service date set to 24/05/2018

> 969565    2018-06-20 14:15:01.000 17530   Copy of NtO produced
> 969565    2018-06-14 19:58:03.000 148 NTO service date set to 24/05/2018

to this:

> 1058841   2018-06-14 19:59:03.000 Copy of NtO produced
> 1058841   2018-06-14 19:58:03.000 NTO service date set to 24/05/2018

> 969565    2018-06-14 19:59:03.000 17530   Copy of NtO produced
> 969565    2018-06-14 19:58:03.000 148 NTO service date set to 24/05/2018

my code currently is as follows:

declare @thisdate table
(
thisdate datetime,
thisref nvarchar(50)
)

declare @thisdate2 table
(
thisdate2 datetime
)


insert into @thisdate(thisdate) (select te_date from (
select row_number() over (partition by te_system_ref order by (select 0)) as rownumber, te_date from ticket_events where te_system_ref in 
(select sl_system_ref from statutory_letter where sl_letter_batch = 9429)and te_event = 'Copy of NtO produced' and te_system_ref = 969565
) t where rownumber = 1)
;

insert into @thisdate(thisref) select te_system_ref from (
select row_number() over (partition by te_system_ref order by (select 0)) as rownumber, te_system_ref from ticket_events where te_system_ref in 
(select sl_system_ref from statutory_letter where sl_letter_batch = 9429)and te_event = 'Copy of NtO produced' and te_system_ref = 969565
) t where rownumber = 1
;

insert into @thisdate2(thisdate2) select te_date from (
select row_number() over (partition by te_system_ref order by (select 0)) as rownumber, te_date from ticket_events where te_system_ref in 
(select sl_system_ref from statutory_letter where sl_letter_batch = 9429)and te_event like 'NTO service date set to%' and te_system_ref = 969565
) t where rownumber = 1
;


update ticket_events
set te_date = dateadd(minute,1,(select thisdate2 from @thisdate2))
where te_date in (select thisdate from @thisdate) and te_system_ref in (select thisref from @thisdate) and te_event = 'Copy of NtO produced'

However the problem with this is if the select query used to populate the variables produces more than one result, it no longer works. How would I resolve this?

And a quick bonus question, how do I take the last row instead of the first using row numbers?

Any help at all will be greatly appreciated!

To answer your "bonus question"; if you had a deterministic `ORDER BY` clause in your `ROW_NUMBER()` statement then you could swap it from the first row to the last by simply adding `DESC`. Now the last row is numbered #1 instead of the first row. — Richard Hansell, Jun 26 '18 at 14:55
Thanks for the response! But i'm sorry i'm not quite sure what you mean by a deterministic order by clause. How would I go about implementing that? — james mcrow, Jun 26 '18 at 15:11
If you ordered by something like a date then it would have a defined (deterministic) order, but ordering by `SELECT 0` doesn't apply a "real" order. — Richard Hansell, Jun 26 '18 at 15:20
https://stackoverflow.com/questions/18613055/is-order-by-and-row-number-deterministic — Richard Hansell, Jun 26 '18 at 15:23

score 0 · Answer 1 · answered Jun 26 '18 at 15:10

I probably didn't totally follow what you are trying to achieve here, but I think the basic issue is that you haven't referenced what you are attempting to update fully. So when you go back to do the update you aren't sure exactly what is supposed to be updated?

I don't see why you are even using table variables, this can all be achieved with a single query I think?

My first attempt looks like this:

WITH Updates AS (
    SELECT
        sl.sl_letter_batch, --not really sure how important this is, but it probably needs joining into the final query
        te.te_system_ref,
        MAX(CASE WHEN te.te_event = 'Copy of NtO produced' THEN te_date END) AS original_date,
        MAX(CASE WHEN te.te_event LIKE 'NTO service date set to%' THEN DATEADD(MINUTE, 1, te_date) END) AS new_date
    FROM
        ticket_events te
        INNER JOIN statutory_letter sl ON sl.sl_system_ref = te.te_system_ref
    GROUP BY
        sl.sl_letter_batch,
        te.te_system_ref)
UPDATE
    te
SET
    te_date = new_date
FROM
    ticket_events te
    INNER JOIN Updates u ON u.te_system_ref = te.te_system_ref AND u.original_date = te.te_date
WHERE
    te.te_event = 'Copy of NtO produced';

...but with no test data this is really just writing a query blind.

Hi Richard, thanks for the response! I'm currently trying to implement your method now. It seems to be error free, however I'm a bit wary about running it as it doesn't seem to like being surrounded by transaction clauses. Stating the error "Incorrect syntax near the keyword 'AS'" for line 3. Also to answer your comment, the "select sl_system_ref from statutory_letter where sl_letter_batch = 9429" is the query that provided me the list of system references that have events in the ticket_event table that need to be updated — james mcrow, Jun 26 '18 at 15:25
Cool, you might need to be careful with semi-colons near `WITH` statements. That's probably the basis of your syntax error? — Richard Hansell, Jun 26 '18 at 15:35

score 0 · Answer 2 · answered Jun 26 '18 at 15:12

You are on the right path using row_number but your implementation is a little bit off.

To get one minute increments from the initial te_date you need both the initial te_date value and an incremental number for each additional row. This can be provided via the row_number function as follows:

declare @t table(te_system_ref int,te_date datetime, te_event varchar(100));
insert into @t values
 (1058841,'2018-06-20 14:15:04.000','Copy of NtO produced')
,(1058841,'2018-06-14 19:58:03.000','NTO service date set to 24/05/2018')
,(969565, '2018-06-20 14:15:01.000','17530   Copy of NtO produced')
,(969565, '2018-06-14 19:58:03.000','148 NTO service date set to 24/05/2018')
;

with d as
(
    select te_system_ref
            ,te_date
            ,te_event    -- row_number gets your minute increments
            ,row_number() over (partition by te_system_ref order by te_date) - 1 as rn
    from @t
)
select te_system_ref    -- min() over function gets the earliest date within the te_system_ref group, which we can add the incremental minutes to.
        ,dateadd(minute,rn,min(te_date) over (partition by te_system_ref)) as te_date
        ,te_event
from d
order by te_system_ref
        ,te_date;

Output:

+---------------+-------------------------+----------------------------------------+
| te_system_ref |         te_date         |                te_event                |
+---------------+-------------------------+----------------------------------------+
|        969565 | 2018-06-14 19:58:03.000 | 148 NTO service date set to 24/05/2018 |
|        969565 | 2018-06-14 19:59:03.000 | 17530   Copy of NtO produced           |
|       1058841 | 2018-06-14 19:58:03.000 | NTO service date set to 24/05/2018     |
|       1058841 | 2018-06-14 19:59:03.000 | Copy of NtO produced                   |
+---------------+-------------------------+----------------------------------------+

how to update multiple rows using list stored in table variables in SQL

2 Answers2