By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `pers` (
`persID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(35) NOT NULL,
`gehalt` int(11) NOT NULL,
`chefID` int(11) DEFAULT NULL,
PRIMARY KEY (`persID`)
) ;
INSERT INTO `pers` (`persID`, `name`, `gehalt`, `chefID`) VALUES
(1, 'blb', 1000, 3),
(2, 'as', 1000, 3),
(3, 'chef', 1040, NULL);
UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE (P.chefID IS NOT NULL
OR gehalt <
(SELECT (
SELECT MAX(gehalt * 1.05)
FROM pers MA
WHERE MA.chefID = MA.chefID)
AS _pers
))
Table 'P' is specified twice, both as a target for 'UPDATE' and as a separate source for data
SELECT * FROM pers;
| persID | name | gehalt | chefID |
|---|---|---|---|
| 1 | blb | 1000 | 3 |
| 2 | as | 1000 | 3 |
| 3 | chef | 1040 | null |