how to update rowNumber

Question

This should be very easy question, but I could not figure out. In mysql table, two of columns are questionNumber and rowNumber. I want to update the rowNumber order by questionNumber. here is my php, and the problem is in query ("UPDATE Question SET rowNumber = ($x=$x+1) ORDER BY QuestionNumber"). Any helper to fix it for me?

<?php

    $link = mysqli_connect($dbhost, $username, $dbpass, $database);

    if (!$link) {
        echo "Error: Unable to connect to MySQL." . PHP_EOL;
        echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
        echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
        exit;
    }

    $x = 0;
    $sql = "UPDATE Question SET rowNumber = ($x=$x+1) ORDER BY QuestionNumber";

    if ($link->query($sql) === TRUE) {
        echo "Updated";
    } else {
        echo "Error updating record: " . $link->error;
    }
    $link->close();
    ?>

Yes. I have two column, and the `questionNumber` always changing. also I want to change the `rowNumber` based on `questionNumber`. Thanks. — , Jul 05 '16 at 01:52
idk update with a join on a derived table that benefits from an order by. Why do you want to do this anyway, this [XY Problem](http://xyproblem.info/) allegedly. — Drew, Jul 05 '16 at 02:10
@Drew I did not understand your comment. I am new to mysql and php, this is small portion of my iPhone app. what do you mean derived table? and idk update? and how to solve this? — , Jul 05 '16 at 02:15
Remove the `ORDER BY` clause. You don't need that with `UPDATE`. — BizzyBob, Jul 05 '16 at 02:35
And do the x=$x+1 before your query, just do the query as: UPDATE Question SET rowNumber = $x — BizzyBob, Jul 05 '16 at 02:37
Abdullah, the reason @FirstOne and I were asking about why, was because, you may be saving data (rownum) in a table that have "visual ordering numbers" in it will become stale and useless or error prone. We were reaching for some understanding of why. — Drew, Jul 05 '16 at 02:52
@BizzyBob thank you very much for your comment. in this table there are 20 column, there is only example I give because I want to make it easy. If I delete the `order by`, than how to give rowNumber based on `questionNumber`? And I do not need where clause because I give number to all row. — , Jul 05 '16 at 23:20

score 1 · Answer 1 · answered Jul 05 '16 at 02:36

1

Maybe you can do it without $x, try following:

UPDATE t1 Question t1
INNER JOIN (
    SELECT @rowno := @rowno + 1 AS rowno, QuestionNumber
    FROM Question
    CROSS JOIN (SELECT @rowno := 0) t
    ORDER BY QuestionNumber
) t2
ON t1.QuestionNumber = t2.QuestionNumber
SET t1.rowNumber = t2.rowno

answered Jul 05 '16 at 02:36

Blank

12,308
1
14
32

after take out first t1, this is for sql working perfectly, but for php it does not work. It makes my all Number column 0. – Jul 05 '16 at 23:13

score 1 · Accepted Answer · answered Jul 05 '16 at 02:37

Here is a visual I just put together. Imagine a table with city and state, and, well, a rownum column !

I want to update the rownum column, but only for the rows with state = South Carolina ... SC

And I want the update order to be by city name. And the data is inserted in physical order originally to show it worked by having SC city names not originally inserted in alpha order.

Schema:

drop table if exists locat123;
create table locat123
(   id int auto_increment primary key,
    city varchar(100) not null,
    state varchar(100) not null,
    rownum int not null
);
insert locat123 (city,state,rownum) values
('a1','NY',-1),('a2','NY',-1),('a3','NY',-1),('a4','NY',-1), 
('m1','MT',-1),('m2','MT',-1),
('s8','SC',-1),('s2','SC',-1),('s4','SC',-1),('s1','SC',-1),('s11','SC',-1);

The Update statement with the derived table:

update locat123 l
join
(   select l.id,l.city,@rn:=@rn+1 as rown
    from locat123 l
    cross join (select @rn:=0) params
    where l.state='SC' -- <==================== right there, update SC only
    order by l.city -- By the way, 5 rows that are South Carolina (SC) in here
) xDerived
on l.id=xDerived.id
set l.rownum=xDerived.rown;
-- 5 rows updated

Results:

select * from locat123 order by state,city;

+----+------+-------+--------+
| id | city | state | rownum |
+----+------+-------+--------+
|  5 | m1   | MT    |     -1 |
|  6 | m2   | MT    |     -1 |
|  1 | a1   | NY    |     -1 |
|  2 | a2   | NY    |     -1 |
|  3 | a3   | NY    |     -1 |
|  4 | a4   | NY    |     -1 |
| 10 | s1   | SC    |      1 |
| 11 | s11  | SC    |      2 |
|  8 | s2   | SC    |      3 |
|  9 | s4   | SC    |      4 |
|  7 | s8   | SC    |      5 |
+----+------+-------+--------+

So why the derived table? Because we had to introduce a variable to increment as our counter. And we used the cross join for the sole purpose of getting that variable into the whole thing. And after the derived table is resolved, we fold up its results into the normal Update with a Join pattern that wrapped it.

Naturally, as user FirstOne said, we can use Update ... order by in some situations. The above is what I came up with for this one.

Oh, and just to reiterate, derived tables are often used to cleanse our custom crafted info and folding it into the greater part of our query.

You too boss...tacked on a +1 for you. Who actually focused on his stuff while I was off in SC somewhere. — Drew, Jul 05 '16 at 02:40
Thank you very much. I understand this. It is worked in sql. Now new problem is how to change it to query in php. Because in my table user can change the questionNumber,( when delete question and add question). I want to update the whole rowNumber column based on question number, and at that time rowNumber will change. so I want to change the rowNumber whenever the question number change. thank you. — , Jul 05 '16 at 23:43
Put it in a stored proc (one you dream up like the answer from @10086 ) or one you craft. You can pass parameters. The stored proc will protect your `@rn` variable or his `@rowno` var) ... otherwise, see this link: .... — Drew, Jul 05 '16 at 23:49
Ok the link is [Here](http://stackoverflow.com/a/32145480) it took me a second to find it as it was on an older question / recent edit I did. So, look at the "Revision1 (PHP shown)" part, and the Heredoc (a chunk `<< — Drew, Jul 05 '16 at 23:56
So if what we are showing you works (@10086 and I), but it did not work in PHP, then it is likely or could be a var issue. A stored proc would protect you from that, or a multi-query [mysqli multi](http://php.net/manual/en/mysqli.multi-query.php) or a PDO equivalent. But, I would just go with a stored proc — Drew, Jul 05 '16 at 23:59
But in either case, come join me in the [Campaigns](http://chat.stackoverflow.com/rooms/95290) chat room sometime. Ping me if I am not there (my keyboard will beep). You may be stumbling into that problem I mentioned of stale data back up in the main comments under your question. No big deal, we can figure it out. — Drew, Jul 06 '16 at 00:08
@Drew Finally I figure out. Thank you very much. you helped a lot. — , Jul 08 '16 at 01:04

how to update rowNumber

2 Answers2