Target table not updatable? Creating a sequence column with ORDER BY using MySql 5.5.62

Question

I'm inputting the following query using MySql 5.5.62, but getting an error message that the table is not updatable?

SET @row_number = 0
> OK
> Time: 0,064s


UPDATE `tbl_a` t
JOIN ( SELECT ( @row_number := @row_number + 1 ) pNum, 
pDateHour, pPrio, pID FROM `tbl_a` ORDER BY pDateHour ASC ) q ON t.pID = q.pID 
SET t.pPrio = q.pNum
> 1288 - The target table q of the UPDATE is not updatable
> Time: 0,068s

What I'm trying to achieve is to by adding a sequence column that I can sort my results on instead of sorting on multiple columns. It's based on an ORDER BY clause involving 1 column.

I need this result

+-------+---------------------+
| pPrio | pDateHour           |
+-------+---------------------+
|    1  | 2021-09-16 18:40:02 |
|    2  | 2021-09-16 19:00:20 |
|    3  | 2021-09-16 19:20:47 |
|    4  | 2021-09-16 20:00:59 |
|    5  | 2021-09-16 20:01:48 |
|    6  | 2021-09-16 20:20:31 |
|    7  | 2021-09-16 20:40:05 |
+-------+---------------------+

marc_s · Accepted Answer · 2021-10-19 14:52:11Z

1

The sequence column you seem to want is actually a derived column based on pDateHour. Therefore, it might make more sense to actually generate this sequence at the time you query, rather than doing an update:

SELECT (@rn := @rn + 1) AS pPrio, pDateHour
FROM yourTable, (SELECT @rn := 0) AS x
ORDER BY pDateHour;

edited Oct 19, 2021 at 14:52

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 16, 2021 at 18:57

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Target table not updatable? Creating a sequence column with ORDER BY using MySql 5.5.62

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related