EN
MySQL - UPDATE query with LIMIT
0 points
In this article, we would like to show you UPDATE query with LIMIT in MySQL.
To show you UPDATE query with LIMIT, we will use the following users table:
Note:
At the end of this article you can find database preparation SQL queries.
In this example, we will update department_id column value for users from id value 3 (excluding user with id = 3). We will also limit the update to 4 users.
Query:
xxxxxxxxxx1
UPDATE `users` SET `department_id`= 102
WHERE `id` IN (3
SELECT `id` FROM (4
SELECT `id` FROM `users` 5
ORDER BY `id` ASC 6
LIMIT 3, 47
) subquery8
);Result:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE `users` (2
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,3
`name` VARCHAR(50) NOT NULL,4
`surname` VARCHAR(50) NOT NULL,5
`department_id` INT(10) UNSIGNED,6
`salary` DECIMAL(15,2) NOT NULL,7
PRIMARY KEY (`id`)8
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO `users`2
( `name`, `surname`, `department_id`, `salary`)3
VALUES4
('John', 'Stewart', 1, '3512.00'),5
('Chris', 'Brown', 2, '1344.00'),6
('Kate', 'Lewis', 3, '6574.00'),7
('Ailisa', 'Gomez', 1, '6500.00'),8
('Gwendolyn', 'James', 2, '4200.00'),9
('Simon', 'Collins', 4, '3320.00'),10
('Taylor', 'Martin', 2, '1500.00'),11
('Andrew', 'Thompson', 1, '2100.00');