CODEWITHSUNDEEP
mysqlsqldatabase
HiddenHopes
HiddenHopes

Reputation: 63

How can I re-ordered the value of a column with some condition?

See the last two row where option_order is 0 but type is different. I want to keep them on first position for each type. How can I re-ordered the value of option_order column?

Here, the condition is, the '000' choice must be kept on first for each type by setting its position_order.

current table status:

MULTIPLE_CHOICE Table

id  choice  type  option_order
1    AA      1       1
2    BB      1       2
3    CC      1       3
4    AAA     2       4
5    BBB     2       5
6    CCC     2       6
7    DDD     2       7
8    000     1       0
9    000     2       0

Required updated table: This is what I need:

updated MULTIPLE_CHOICE Table

id  choice  type  option_order
8    000     1       1
1    AA      1       2
2    BB      1       3
3    CC      1       4
9    000     2       5
4    AAA     2       6
5    BBB     2       7
6    CCC     2       8
9    DDD     2       9

The actual table is too big, so I cannot do this by edit. Please help for this complex query. I have no clue to solve this.

[Note: I need this to solve for mysql version 5.7]

Upvotes: 0

Views: 33

Answers (1)

Akina
Akina

Reputation: 42728

Recalculate the whole column. Use, for example, user-defined variable:

UPDATE MULTIPLE_CHOICE
SET option_order = (@counter := @counter + 1)
WHERE (SELECT @counter := 0) = 0
ORDER BY type, choice;

fiddle

can you explain this condition: WHERE (SELECT @counter := 0) = 0. – HiddenHopes

This is a condition only formally - as you can see it is always TRUE. The aim of this construction is in variable initialization.

In SELECT queries we can initialise user-defined variables in separate subquery cross-joined to another tables, like:

SELECT {columnset}
FROM {tableset}
CROSS JOIN ( SELECT @variable := starting_value ) AS initialize_variables
{the rest clauses}

But we cannot do the same in UPDATE. The calculations are critically dependent by rows processing order, i.e. ORDER BY clause with the ordering expression which provides rows uniqueness is compulsory. But the subquery which initializes the variables will convert single-table UPDATE to multiple-table which does not support ORDER BY clause at all!

The way out of this situation is to initialize the variable in WHERE clause. When server builds query execution plan it evaluates all constant expressions, including ones in WHERE clause. Moreover, in UPDATE server MUST evaluate WHERE expression before updating because it must firstly determine the rows which will be updated, and only then update them. So the expression in WHERE will be evaluated before rows updating, and hence the variable will be initialized before rows iteration with guarantee.

Upvotes: 1

Related Questions