how to use group by in update form ? in SQL-CodePudding

first, I search name who teaches more than 2 courses

SELECT i.name, COUNT(*) teaches FROM instructor i
INNER JOIN teaches t ON i.id = t.id
GROUP BY name
HAVING COUNT(*)>=2;

and it's working so I want to raise salary with these results

UPDATE i SET salary = salary*1.05
FROM instructor i
INNER JOIN teaches t ON i.id = t.id
GROUP BY name HAVING COUNT(*)>=2;

but an error in SQL syntax; how to fix it?

CodePudding user response：

I would use an update join here:

UPDATE instructor t1
INNER JOIN
(
    SELECT i.id
    FROM instructor i
    INNER JOIN teaches t ON i.id = t.id
    GROUP BY i.id
    HAVING COUNT(*) > 1
) t2
    ON t2.id = t1.id
SET
    salary = 1.05 * salary;