CODEWITHSUNDEEP
sqlcount
user3067808
user3067808

Reputation: 11

update using count from another table

I want update wage in the staff table based on the staff members who have rented the most cars

UPDATE staff 
SET wage = wage + 5000 

WHERE COUNT(staffid) >= ALL
Select COUNT(staffid) FROM carforrent)

Upvotes: 1

Views: 76

Answers (3)

Luis de Haro
Luis de Haro

Reputation: 739

with cte
as
(
    select top 1 count(1) maxcount
    from carforrent
    group by staffid
    order by count(1) desc
)
update staff
set wage = wage + 5000
where staffid in(
    select staffid
    from carforrent
    group by staffid
    having count(1) = (select maxcount from cte)
);

I did not test it, hope it helps.

Good luck.

Upvotes: 1

db9dreamer
db9dreamer

Reputation: 1715

update s set
    s.wage = s.wage + 5000 
from staff s
join (
    select staffid
    from carforrent
    group by staffid
    having count(*) = (
        select top 1 count(*)
        from carforrent
        group by staffid
        order by count(*) desc
    )
) sq on sq.staffid=s.staffid

The innermost query finds the highest number of cars rented by any member of staff. This number is used in a having - to identify all the members of staff that rented that number of cars. That query is then joined back onto the staff table to filter just the staff with a burning desire to rent cars - so that the update only gives them a pay rise.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You don't specify the database you are using, but you want something like this:

update staff
    set wage = wage + 5000
    where staffid in (select top 1 staffid
                      from carforrent
                      group by staffid
                      order by count(*) desc
                     );

This is SQL Server syntax, but similar constructs work in most databases.

Upvotes: 1

Related Questions