Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Is it possible to use UPDATE query with LIMIT in MySQL?
Yes, it is possible to use UPDATE query with LIMIT in MySQL. Let us see how.
For our example, we will first create a table. The CREATE command is used to create a table.
mysql>CREATE table tblUpdateLimit -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.53 sec)
Records are inserted with the help of INSERT command.
mysql>INSERT into tblUpdateLimit values(1,'John'); Query OK, 1 row affected (0.54 sec) mysql>INSERT into tblUpdateLimit values(2,'Carol'); Query OK, 1 row affected (0.12 sec) mysql>INSERT into tblUpdateLimit values(3,'Smith'); Query OK, 1 row affected (0.10 sec) mysql>INSERT into tblUpdateLimit values(4,'Kayle'); Query OK, 1 row affected (0.44 sec) mysql>INSERT into tblUpdateLimit values(5,'David'); Query OK, 1 row affected (0.13 sec) mysql>INSERT into tblUpdateLimit values(6,'Jason'); Query OK, 1 row affected (0.18 sec) mysql>INSERT into tblUpdateLimit values(7,'Larry'); Query OK, 1 row affected (0.15 sec) mysql>INSERT into tblUpdateLimit values(8,'Serhat'); Query OK, 1 row affected (0.15 sec) mysql>INSERT into tblUpdateLimit values(9,'Winny'); Query OK, 1 row affected (0.18 sec)
To display the above table, here is the query.
mysql> SELECT *from tblUpdateLimit;
The following is the output.
+------+--------+ | id |name | +------+--------+ | 1 | John | | 2 | Carol | | 3 | Smith | | 4 | Kayle | | 5 | David | | 6 | Jason | | 7 | Larry | | 8 | Serhat | | 9 | Winny | +------+--------+ 9 rows in set (0.00 sec)
Let us now see the syntax to use UPDATE query with limit.
UPDATE yourTableName SET column_name='some value’' WHERE column_name1 IN ( SELECT column_name1 FROM ( select column_name1 from yourTableName order by column_name1 asc limit integerValue,integerValue) anyAliasName );
Implementing the query now to fulfil our purpose and using it to set the name ‘Adam’, with limit 7.
mysql> UPDATE tblUpdateLimit SET name = 'Adam' -> WHERE id IN ( SELECT id FROM ( select id from tblUpdateLimit order by id asc limit 0,7)l); Query OK, 7 rows affected (0.27 sec) Rows matched: 7 Changed: 7 Warnings: 0
Check whether the table is updated or not.
mysql> SELECT *from tblUpdateLimit;
Here is the output.
+------+--------+ | id | name | +------+--------+ | 1 | Adam | | 2 | Adam | | 3 | Adam | | 4 | Adam | | 5 | Adam | | 6 | Adam | | 7 | Adam | | 8 | Serhat | | 9 | Winny | +------+--------+ 9 rows in set (0.00 sec)
Updated on: 26-Jun-2020
7K+ Views
Advertisements