UPDATE particular row in MYSQL using a row number

Question

How to update a particular row from MYSQL DB when v r given a row number liek update username at row =5 , n nothing else is given , v dont want 2 use the WHERE Clause , all v know is row number

so how ??

UPDATE  users set status=7 limit 5,1; // this is not working error in sql syntax

want 2 update 6th row

users=table name

same what will b the syntax for delete statement for the same operation ?

i m luking 4 a better ans , post it if i like it i will accept urs — Info_wars, Aug 28 '16 at 09:32
Table in mysql are considered to be unordered set of data unless you explicitly specify an order like `ORDER BY `. So in your case what's that ``? — 1000111, Aug 28 '16 at 09:34
I m not ordering them at all , i m asking 4 he data as it has been appended in the table as it is .Say users keep entering their name n d data keeps getting appended at the bottom , i m calling them in the order the were added in the database . means data gets appended in the bootom , so 1st user who registered comes 1st n last in d last — Info_wars, Aug 28 '16 at 09:43
Btw, Are you playing with accepting answer? I noticed initially you accepted `GEEkSpoTTed's` answer then `Dev Joel's` answer then again unchecking `Dev Joel's answer` you accepted `GEEkSpoTTed's` answer. — 1000111, Aug 28 '16 at 09:46
@ 1000111 I m getting this error when i use ur statement with java NetbeansThis version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' — Info_wars, Aug 28 '16 at 10:30
@1000111 - your intuition was spot on, they were [sockpuppet accounts](http://meta.stackexchange.com/questions/57682/how-should-sockpuppets-be-handled-on-stack-exchange). It has been corrected by the moderators. — Igor, Oct 20 '16 at 11:22

score 1 · Accepted Answer · edited May 23 '17 at 12:16

Read this post: Select specific row from mysql table

Now once u have selected a row,find a column whose data is unique say id or username ,email ,anything that is unique for a row. Lets say username is unique for ur table

Then do this

SELECT username FROM customer LIMIT 5,1;

Store it in a variable if u r using java/php/etc. now say u stored in variable var. then using this run another sql command

update users set status=7 where username=var ;

//var is the varible name that u assigned value to.

Dev. Joel · Answer 2 · 2016-08-28T09:38:10.883

1

you can try something like a nested where the limit accepts two parameters 0 and 2 start where number of rows

UPDATE users SET status=1
WHERE userid IN (
 SELECT userid FROM (
     SELECT userid FROM users 
     LIMIT 0, 2
 ) tmp

);

edited Aug 28 '16 at 09:38

answered Aug 28 '16 at 09:33

Dev. Joel

1,127
1
9
14

You need an alias for a subquery – Dev. Joel Aug 28 '16 at 09:37

UPDATE particular row in MYSQL using a row number

2 Answers2