why limit wont work for updats statement

vivekanandan · #1 (**permalink**) 08-06-2007, 05:07 AM

why we cant use limit in the update statement ?

priyan · #2 (**permalink**) 08-06-2007, 05:50 AM

Hi vivek,

you can use limit function in the update statement but should avoid start and end position within limit, i mean we should not use limit like 'LIMIT 1,2' instead use LIMIT 2. This will update first two rows that
satisfies the condition.

Refer the below given example :

Quote:

mysql> select * from e_employee;
+----+---------+--------+---------+--------+---------+
| id | name | salary | address | phone | dept_id |
+----+---------+--------+---------+--------+---------+
| 1 | kumar | 15000 | chennai | 343424 | 10 |
| 2 | ram | 15000 | chennai | 343424 | 10 |
| 3 | arun | 15000 | chennai | 343424 | 20 |
| 4 | karthik | 15000 | chennai | 343424 | 30 |
+----+---------+--------+---------+--------+---------+

4 rows in set (0.00 sec)

mysql> UPDATE e_employee set salary = 20000 where salary = 15000 limit 2;
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from e_employee;
+----+---------+--------+---------+--------+---------+
| id | name | salary | address | phone | dept_id |
+----+---------+--------+---------+--------+---------+
| 1 | kumar | 20000 | chennai | 343424 | 10 |
| 2 | ram | 20000 | chennai | 343424 | 10 |
| 3 | arun | 15000 | chennai | 343424 | 20 |
| 4 | karthik | 15000 | chennai | 343424 | 30 |
+----+---------+--------+---------+--------+---------+
4 rows in set (0.00 sec)

Murali · #3 (**permalink**) 08-06-2007, 06:09 AM

Hi,

mysql> CREATE TABLE mytable
-> ( id INTEGER AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(100) NOT NULL
-> )type=InnoDB;
Query OK, 0 rows affected, 1 warning (0.21 sec)

mysql> INSERT INTO mytable VALUES (1,'Test');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO mytable VALUES (2,'CanTest');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO mytable VALUES (3,'WillbeTested');
Query OK, 1 row affected (0.02 sec)

mysql> UPDATE mytable SET name = 'Completed' LIMIT 2;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0

LIMIT works fine with update statement.

Hope this helps..

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Limit on Sub-folders / Files in Linux	bluesky	Operating Systems	5	03-31-2008 11:03 PM
Max User Connection, can limit be increased?	senraj	PHP Programming	2	12-03-2007 01:59 AM
Gsm sim wont give IMSI?	jeyaprakash.c	Blackberry	0	11-26-2007 06:50 AM
File Upload Size Limit	KiruthikaSambandam	ASP and ASP.NET Programming	9	09-27-2007 03:16 AM
How do I limit the number of rows I get out of my database	sans	PHP Programming	1	09-12-2007 12:20 AM