MySQL Update Query - By Row Number

Question

I'd like to update a row in a MySQL table when a specific row number is reached

This is a little confusing since , the real row number of the record isn't a column in the table.

And there's no question of iterating over rows , since we're not iterating over an array as in mysql_fetch_array()

So , if I'd like to update - say the 3rd row of the table , what would the query be like?

I'm a noob at MySQL

Thanks a ton for your help ! :D

There is no such thing as a '3-rd row in a table'. Tables don't have a predefined order. — Vatev, Apr 12 '13 at 08:01
you cannt update like that .. there has to be something unique to identify the row — alwaysLearn, Apr 12 '13 at 08:02
I don't think mysql supports row number, but checkout links http://stackoverflow.com/questions/3126972/mysql-row-number for some kind of fix, if it helps. — kuldeep.kamboj, Apr 12 '13 at 08:02

Amir · Accepted Answer · 2013-04-13T04:29:13.057

$link = mysqli_connect("localhost", "my_user", "my_password", "my_db");
$query = "SELECT MyColoumn FROM Mytable";
$result = mysqli_query($link, $query);

$row_needed = 3; //Your needed row e.g: 3rd row
for ($i=1,$i=$row_needed,$i++) {
$row = mysqli_fetch_array($result); 
}
// now we are in 3rd row
$query = "UPDATE MyColumn FROM MyTable SET MyColumn = '".$MyColumnUpdate."' WHERE MyColumn = '".$row['MyColumn']."' ";
$result = mysqli_query($link, $query);
...

score 0 · Answer 2 · answered Apr 12 '13 at 08:07

Try this query

UPDATE 
   tbl a, 
   (Select 
       @rn:=@rn+1 as rowId, 
       tbl.* 
   from 
       tbl 
   join 
       (select @rn:=0) tmp) b
SET 
   a.columnName = <VALUE>
WHERE 
   b.rowId = <rowNumber> AND
   a.id = b.id;

NOTE The id column must be a unique one, you can use the primary key of that table...

MySQL Update Query - By Row Number

2 Answers2

SQLFIDDLE