CODEWITHSUNDEEP
mysql
Edward Sheriff Curtis
Edward Sheriff Curtis

Reputation: 497

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

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 |
+-------+---------------------+

Upvotes: 0

Views: 33

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521804

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;

Upvotes: 1

Related Questions