MariaDB: update with order by

[oOLooCoreZOo]

Hi,

I want to execute the following SQL statements:

SET @cur_pos = 0;
UPDATE table
SET position = (SELECT (@cur_pos := @cur_pos + 1000))
WHERE tableId = 3570
ORDER BY position DESC;

await connection.queryRunner.executeSelectOneRow('SET @cur_pos = 0');
const newPostion = connection.fragmentWithType('int', 'required').sql`(SELECT (@cur_pos := @cur_pos + 1000))`;
const reorder = await connection.update(table)
    .set({
        position: newPostion
    })
    .where(table.tableId.equals(3570))
    .customizeQuery({
        afterQuery: connection.rawFragment`ORDER BY position DESC`
    })
    // .orderBy(table.position)
    .executeUpdate();

ts-sql-query does not support ORDER BY in an update statement.
It would be great if this could be added.
Please see https://mariadb.com/kb/en/update/ for reference.
This only seems to be supported by MySQL and MariaDB.

Do you plan to integrate user defined variables into the API so I can avoid custom SQL?

Best Regards,
Chris

[juanluispaz]

I took note of this!

Can you elaborate a little bit more on your idea about user-defined variables?

Right now, I see the use case that you put in here, but I haven't figured out the general use case yet. Can you describe a little bit more how it is used in a case?

I see you want the @cur_pos, but I would like to hear why this needs to be set as an independent variable inside the transaction.

There is another topic, maybe related, that is an update that is able to return in the same way almost all big other databases do. I've been following MariaDB. They already implemented insert and delete returns, but the update stagnated; now, there are some movements to implement it. See https://jira.mariadb.org/browse/MDEV-5092

I would like to hear back from you.

[oOLooCoreZOo]

Hi,

Of course, I can explain this in detail.
I want to save menu items in my database.
The owner can change the position of the menu items in the list.
To do this, I have added the position to the table.
The goal is to only change an item in the database if its position has changed.
For example:

CREATE TABLE items (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  position INT
);

id | name | position
1 | Item A | 1000
2 | Item B | 2000
3 | Item C | 3000

If I want to move Item C between A and B, I simply need to change the position of Item C to 1500.
As you may have noticed, at some point there may be no space between two items.
In this case, the positions need to be normalized.
For this, I use the following SQL:

SET @cur_pos := 0;

UPDATE items
SET position = (@cur_pos := @cur_pos + 1000)
ORDER BY position ASC;

Please note that the items table is striped and usually contains items from other menus.

I hope this helps to understand the use case.