Auto-increment MySQL column field on update using a single query

Question

Suppose I have this code:

$ids = execute_query("SELECT id FROM table WHERE field = 'value' ORDER BY order_field");
$query = "UPDATE table SET increment = CASE";
for ($i = 0; $i <= sizeof($ids); $i++) {
  $query .= " WHEN id = " . $ids[$i] . " THEN " . $i;
}
$query .= " END WHERE field = 'value'";

Then I execute that query.

Is there a way to combine the SELECT and UPDATE into one single query that achieves the exact same thing efficiently?

In short... No. You can either perform an operation on a record _or_ do a select. You could wrap the logic inside a stored proc which would perform the update then do a select (or vice-versa). You'd only need to make one call to the database but it would still be 2 distinct operations against the table. — Basic, Apr 03 '13 at 15:43
Is the goal to have a field numbered sequentially starting at zero? — Bafsky, Apr 03 '13 at 15:44
@SomeSillyName ... pretty much (also only the fields that matches the WHERE statement and have it ordered similarly to that ORDER statement) — pillarOfLight, Apr 03 '13 at 15:45
You could create a function, to run two without cluttering your code, but as far as I know, you can not perform two different queries in the same line.. — samayo, Apr 03 '13 at 15:46
This may help... http://stackoverflow.com/questions/8607459/return-mysql-original-row-number. But, instead of updating, could you just create/display the numbering on the PHP side while looping through the rows? — Bafsky, Apr 03 '13 at 15:49

score 1 · Accepted Answer · answered Apr 03 '13 at 16:01

Update a table while sub-selecting the same table is not possible: http://dev.mysql.com/doc/refman/5.6/en/update.html "Currently, you cannot update a table and select from the same table in a subquery."

I didn't run this here, but from what I understood this is what you want:

SET @ordering = 0;

UPDATE
  table SET increment = (@ordering := @ordering + 1)
WHERE
  field = 'value'
ORDER BY
  order_field;

You can execute the SETand UPDATEin separated queries, as long as you do not reconnect to the database.

score 0 · Answer 2 · answered Apr 03 '13 at 15:48

0

This might do what you want:

update table
    set increment = (select cnt
                     from (select count(*) as cnt
                           from table t
                           where field = 'value' and t.id <= table.id
                          ) a
                    )
    where field = 'value';

answered Apr 03 '13 at 15:48

Gordon Linoff

1,242,037
58
646
786

How does this select the table row? – Basic Apr 03 '13 at 16:06
@Basic . . . The subquery calculates a sequential value for the ids, which seems to be what the original code is doing (although this starts at 1 and the original code starts at 0). – Gordon Linoff Apr 03 '13 at 16:15

score 0 · Answer 3 · edited Jan 06 '20 at 14:13

0

SET @ordering = 100000;

UPDATE candidate 
    SET regno = (@ordering := @ordering + 1)
    WHERE 
        year = 2014 AND 
        confirm = 1 AND
        e_stat = 1 
    ORDER BY centre_code, `name`;

edited Jan 06 '20 at 14:13

Qrzysio

1,147
3
12
25

answered May 01 '14 at 16:22

deeps

19
5

Welcome to SO. While this code may achieve the desired result, to be considered a good, helpful answer, it's best to include some context as to why/how this address the OP's issue. An explanation will help future visitors learn how to resolve/avoid similar coding issues, and keeps the quality of SO high. Thorough answers are more likely to be upvoted, and less likely to be closed. Please `edit` to provide context, references, or your reasoning behind this code snippet. Thanks! Additional posting guidelines can be found in the SO [help](https://StackOverflow.com/help) section. – SherylHohman Jan 06 '20 at 16:36

Auto-increment MySQL column field on update using a single query

3 Answers3