EN
MySQL - UPDATE query with IF condition (conditional update)
0 points
In this article, we would like to show you UPDATE
query with IF
condition in MySQL.
Quick solution:
xxxxxxxxxx
1
UPDATE `table_name`
2
SET `column_name` = IF(condition , if_true, if_false);
To show UPDATE
query with IF
condition, 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 every department_id
to 11
if it is equal to 1
.
Query:
xxxxxxxxxx
1
UPDATE `users`
2
SET `department_id` = IF( `department_id` = 1 , 11 , `department_id`);
Output:

Note:
Go to this article to see more
IF()
function practical examples.
Optionally you can add WHERE
clause to Example 1.
In this example, we will update every department_id
to 11
if it is equal to 1
but only for users whose id > 3
.
Query:
xxxxxxxxxx
1
SELECT * FROM `users`
2
WHERE `salary` < (SELECT AVG(`salary`)
3
FROM `users`);
Output:

create_tables.sql
file:
xxxxxxxxxx
1
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:
xxxxxxxxxx
1
INSERT INTO `users`
2
( `name`, `surname`, `department_id`, `salary`)
3
VALUES
4
('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');