LIMIT clause 2

[spookie]

Hi,

I just want to confirm if the LIMIT clause is applicable to UPDATE/INSERT query as well.
I have to update certain data (10000 rows) but to be on the safer side i have to do it first on 10 records.
so my query will be like

UPDATE <tablename> SET <KEY> = '<VALUE>'  LIMIT 10 ;

again what 10 records will be updated? Will it update randomly or on certain criteria.

I am using MySQL version 5.0.22 .

Thanks in advance.


--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[r937]

randomly, i think

why don't you try it on a test table

r937.com | rudy.ca

 

[vacunita]

Not quite as random. It will update the first rows it gets to. so if your table looks something like:

ID   Value1 Value2
0	7	4
1	3	5
2	4	2
3	9	3

And you did UPDATE mytable SET value1=100 LIMIT 2

It will update the first 2 rows.

ID   Value1  Value2
0	[red]100[/red]  4
1	[red]100[/red]  5
2	4	2
3	9	3

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[r937]

vacunita, what you said (the first rows it gets to) is accurate

what you showed is misleading, as it shows the rows in sequence by id, and the UPDATE with LIMIT does not follw any sequence

by the way, how do you get an id of 0? ;-)



r937.com | rudy.ca

 

[vacunita]

Whatever the sequence is in your DB. Usually ordered by the PK/ID whatever you want to call it as that's the way they are inserted if its autoincrement. Of course it doesn't have to be but it is usually the case.



As for the 0, yeah sorry about that.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[spookie]

r937, vacunita,

Thanks for your replies.. Actually i was somewhat occupied and could not get back to you earlier.

Anyway i have updated the table successfully



--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.