Ggtcf

I have a table biblek2 items with those 4 columns :

id (autoincrement) 
catid(int) 
introtext(varchar) 
ordering(int)

Table biblek2_items

╔════╦═══════╦═══════════╦══════════╗
║ ID ║ catid ║ introtext ║ ordering ║
╠════╬═══════╬═══════════╬══════════╣
║ 1 ║ 3024 ║ orange ║ 122 ║
║ 2 ║ 2024 ║ zebra ║ 45 ║
║ 3 ║ 3010 ║ juice ║ 55 ║
║ 4 ║ 3002 ║ build ║ 17 ║
║ 5 ║ 2003 ║ car ║ 87 ║
║ 6 ║ 1610 ║ other ║ 1521 ║
║ 7 ║ 1620 ║ other ║ 200 ║
╚════╩═══════╩═══════════╩══════════╝

I expect that

Table biblek2_items

╔════╦═══════╦═══════════╦══════════╗ 
║ ID ║ catid ║ introtext ║ ordering ║
╠════╬═══════╬═══════════╬══════════╣
║ 5 ║ 2003 ║ car ║ 1 ║
║ 4 ║ 3002 ║ build ║ 2 ║
║ 3 ║ 3010 ║ juice ║ 3 ║
║ 1 ║ 3024 ║ orange ║ 4 ║
║ 2 ║ 2024 ║ zebra ║ 5 ║
╚════╩═══════╩═══════════╩══════════╝

I want to

1. select * from biblek2_items where catid between 2001 and 3024


2. ORDER BY introtext ASC


3. empty the ordering column


4. reorder the ordering column by increment from 1 to n according to the result of the order column

I tried this with no success

DECLARE @variable int 
SET @variable = 0 
UPDATE `biblek2_items` 
SET @variable = ordering = @variable + 1 
WHERE ordering IN (SELECT ordering 
 FROM `biblek2_items` 
 WHERE catid BETWEEN 2001 AND 3024 
 ORDER BY `introtext` DESC)

I read in the forum that MySQL can't allow subrequests with ORDER BY, so could you help me

As explained in the comments :
The ORDER BY in your sub query makes no sense anyway, because, you don't LIMIT. So all rows will be returned and it doesn't matter how they are ordered because all of them are taken into account with the IN in your main query.

But there are other issues with your query.

Do this instead :

SET @row_number = 0 ;

UPDATE biblek2_items, 
 (select id, catid,introtext,ordering, (@row_number:=@row_number + 1) AS newordering 
 from biblek2_items 
 where catid between 2001 and 3024
 ORDER BY introtext ASC 
 ) as temp
SET biblek2_items.ordering = temp.newordering
WHERE biblek2_items.ID = temp.ID

Additionally, if you have a large table, and a lot of users actively writing on it, to avoid inconsistencies or locking issues, I would suggest a slightly different method, using a temporary table to store the computed new ordering.

CREATE TABLE biblek2_items_TEMP (ID INT, ordering INT);

SET @row_number = 0 ;

INSERT INTO biblek2_items_TEMP 
select id, (@row_number:=@row_number + 1) AS newordering 
from biblek2_items 
where catid between 2001 and 3024
ORDER BY introtext ASC 
;

UPDATE biblek2_items, biblek2_items_TEMP
SET biblek2_items.ordering = biblek2_items_TEMP.ordering
WHERE biblek2_items.ID = biblek2_items_TEMP.ID;

DROP TABLE biblek2_items_TEMP;

Tested successfully on MySQL 5.7 and MariaDB 10