Updating multiple rows with a certain value from the same table

Question

So, I have the next table:

time    |  name   |  ID  |
12:00:00| access  |  1   |
12:05:00| select  | null |
12:10:00| update  | null |
12:15:00| insert  | null |
12:20:00|   out   | null |
12:30:00| access  |  2   |
12:35:00| select  | null |

The table is bigger (aprox 1-1,5 mil rows) and there will be ID equal to 2,3,4 etc and rows between.

The following should be the result:

time    |  name  |  ID  |
12:00:00| access |  1   |
12:05:00| select |  1   |
12:10:00| update |  1   |
12:15:00| insert |  1   |
12:20:00|   out  |  1   |
12:30:00| access |  2   |
12:35:00| select |  2   |

What is the most simple method to update the rows without making the log full? Like, one ID at a time.

So it has to increment at the point it hits 'access' in the name field? — Rich Benner, Jul 07 '16 at 10:58
You can use update with join . http://stackoverflow.com/a/1604212/1618775 — Umut Catal, Jul 07 '16 at 11:00
Is it absolutely sure, that each *access* starts a new ID-range and there will **never** be any overlapping of time values? — Shnugo, Jul 07 '16 at 11:07
@MisterM, sorry, my bad... Did not knwo, that Sybase ASE is using transact SQL too... — Shnugo, Jul 07 '16 at 11:09

score 2 · Answer 1 · answered Jul 07 '16 at 11:02

2

You can do it with a sub query:

UPDATE YourTable t
SET t.ID = (SELECT TOP 1 s.ID
            FROM YourTable s
            WHERE s.time < t.time AND s.name = 'access'
            ORDER BY s.time DESC)
WHERE t.name <> 'access'

Index on (ID,time,name) will help.

answered Jul 07 '16 at 11:02

sagi

40,026
6
59
84

Problem with `select top 1` as this is sybase as stated in tags. Workaround? And with `t` near the update. Eliminated that. – MisterM Jul 07 '16 at 11:11

score 1 · Answer 2 · answered Jul 08 '16 at 07:23

1

You can do it using CTE as below:

;WITH    myCTE
          AS ( SELECT   time
                      , name
                      , ROW_NUMBER() OVER ( PARTITION BY name ORDER BY time ) AS [rank]
                      , ID
               FROM     YourTable
             )
    UPDATE  myCTE
    SET     myCTE.ID = myCTE.rank 

SELECT  *
FROM    YourTable ORDER BY ID

answered Jul 08 '16 at 07:23

varun kumar dutta

202
1
4
10

+1 for the answer but the beautiful Sybase ASE 15 does not support CTE or the `over partition by` clause. – MisterM Jul 08 '16 at 08:51

Updating multiple rows with a certain value from the same table

2 Answers2