How can I update a column between two unique values in another column in MySQL?

Question

How can I update a column between two unique values in another column in MySQL or MariaDB?

Consider a table called Example that has three columns:

Id: An auto-increment integer ID
RandomId: A series of random and unique GUIDs
IsUpdated: A column currently only containing NULL values, that needs to be updated

* ----------------------------------------------------- *
| Id | RandomId                             | IsUpdated |
| ----------------------------------------------------- |
| 1  | c446980b-cf2f-4f2d-a27b-28d6bde6415d | NULL      |
| 2  | d6a1a52c-d073-4019-836a-67cf6551d958 | NULL      |
| 3  | 7a339a6a-8e57-4373-84fd-1b40ee51c884 | NULL      |
| 4  | 56b908a7-fb07-4f4c-a25d-699cf40cf690 | NULL      |
| 5  | fac75ce6-a605-453a-958c-74f197e20a11 | NULL      |
* ----------------------------------------------------- *

I would like to update IsUpdated between two specific GUIDs, like so:

UPDATE Example
   SET IsUpdated = 1
 WHERE RandomId >= 'd6a1a52c-d073-4019-836a-67cf6551d958' -- Starting Here
   AND RandomId <= '56b908a7-fb07-4f4c-a25d-699cf40cf690' -- Ending Here

The resulting table should look like the following:

* ----------------------------------------------------- *
| Id | RandomId                             | IsUpdated |
| ----------------------------------------------------- |
| 1  | c446980b-cf2f-4f2d-a27b-28d6bde6415d | NULL      |
| 2  | d6a1a52c-d073-4019-836a-67cf6551d958 | 1         |
| 3  | 7a339a6a-8e57-4373-84fd-1b40ee51c884 | 1         |
| 4  | 56b908a7-fb07-4f4c-a25d-699cf40cf690 | 1         |
| 5  | fac75ce6-a605-453a-958c-74f197e20a11 | NULL      |
* ----------------------------------------------------- *

But since the Ids are not sequential, this method does not appear to work.

What would be the most efficient way to update a column (IsUpdated) between two unique values in another column (RandomId)?

The "GIUD between two specific GUIDs" may be defined literally only. You cannot extract any other ordering info from their values (including their generation ordering). The task in its current definition is unsolvable. — Akina, Jun 16 '20 at 19:52
Do you have some other column that orders the data, like a date or auto_increment column? — Barmar, Jun 16 '20 at 19:58
@Barmar For this example, there is not an ordering column because the current order of the table will always be the correct order. Do you think using a variable as a counter would work to simulate an ordering column (like shown here: https://stackoverflow.com/a/1895127/13190771)? — rondoe, Jun 16 '20 at 20:07
@Barmar I agree, and I think an auto-increment Id would make the most sense, but for this example table, it does not exist, and it is safe to assume that there will not be any custom sorting. The order in which the rows have been inserted is the only "order" that should be considered in this example. — rondoe, Jun 16 '20 at 20:12
MySQL makes no guarantee that rows will be processed in the order that they were inserted. — Barmar, Jun 16 '20 at 20:13
@Barmar Good to know. It sounds like another column for ordering is a must. I have updated the question to reflect a third column. — rondoe, Jun 16 '20 at 20:21

score 1 · Accepted Answer · answered Jun 16 '20 at 20:33

You need to filter by id instead. I would recommend the update ... join syntax:

update example e
inner join (
    select min(Id) minId, max(Id) maxId
    from example
    where RandomId in (
        'd6a1a52c-d073-4019-836a-67cf6551d958', 
        '56b908a7-fb07-4f4c-a25d-699cf40cf690'
    )
) i on e.id between i.minId and i.maxId
set e.IsUpdated = 1

Note that this does not stricly guarantee that guid are matched on the first and last rows (it would also work the other way around). You can be more specific with two joins:

update example e
inner join (
    select Id
    from example
    where RandomId = 'd6a1a52c-d073-4019-836a-67cf6551d958'
) eMin on e.id >= eMin.id
inner join (
    select Id
    from example
    where RandomId = '56b908a7-fb07-4f4c-a25d-699cf40cf690'
) eMax on e.id <= eMax.id
set e.IsUpdated = 1

How can I update a column between two unique values in another column in MySQL?

How can I update a column between two unique values in another column in MySQL or MariaDB?

1 Answers1