CODEWITHSUNDEEP
phpmysql
user4410354
user4410354

Reputation:

how to update rowNumber

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();
    ?>

Upvotes: 1

Views: 168

Answers (2)

Drew
Drew

Reputation: 24960

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.

Upvotes: 1

Blank
Blank

Reputation: 12378

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

Upvotes: 1

Related Questions