CODEWITHSUNDEEP
sql-serverdatabaset-sqlstored-procedures
Ger Mc
Ger Mc

Reputation: 640

UPDATE column using COUNT

I want the parameter 'HoursPerWeek' to Equal the number of pupils with a specific Teachers ID. I'm really lost on how to go about this.

CREATE PROC spUpdateTeachingHoursAndWages
AS
update Payments.HoursPerWeek
set HoursPerWeek = (select COUNT(PupilDetails.Teacher_ID)
FROM Assignment.dbo.Payments JOIN Assignment.dbo.PupilDetails 
ON Payments.ID = PupilDetails.Teacher_ID

Upvotes: 0

Views: 66

Answers (3)

gofr1
gofr1

Reputation: 15977

The answer of @McNets is correct, but there can be modifications:

Better use table aliases and there is no need for sub-query:

CREATE PROC spUpdateTeachingHoursAndWages --(@Teacher_ID int)
AS
UPDATE p
SET HoursPerWeek = COUNT(pd.Teacher_ID)
FROM Assignment.dbo.Payments p
INNER JOIN Assignment.dbo.PupilDetails pd
    ON p.ID = pd.Teacher_ID
--WHERE p.ID = @Teacher_ID
--Uncomment this, if you need to change HoursPerWeek for some special teacher

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

Without using a specific Teacher_ID parameter:

Correlated subquery version:

create proc spUpdateTeachingHoursAndWages as
begin;
set nocount, xact_abort on;
  update p 
  set HoursPerWeek = (
      select count(*)
      from Assignment.dbo.PupilDetails pd
      where pd.Teacher_id = p.id
      )
  from Assignment.dbo.Payments p;
end;
go

inner join version:

create proc spUpdateTeachingHoursAndWages as
begin;
set nocount, xact_abort on;
  update p 
  set HoursPerWeek = pd.HoursPerWeek
  from Assignment.dbo.Payments p
    inner join (
      select 
          pd.id
        , HoursPerWeek = count(*) 
      from Assignment.dbo.PupilDetails pd
      group by pd.id
      ) pd
      on p.id = pd.Teacher_id;
end;
go

cross apply() version:

create proc spUpdateTeachingHoursAndWages as
begin;
set nocount, xact_abort on;
  update p 
  set HoursPerWeek = pd.HoursPerWeek
  from Assignment.dbo.Payments p
    cross apply(
      select HoursPerWeek = count(*) 
      from Assignment.dbo.PupilDetails pd
      where p.id = pd.Teacher_id;
      ) pd
end;
go

Note: Why you should always include set xact_abort, nocount on; - Erland Sommarskog

Upvotes: 0

McNets
McNets

Reputation: 10807

If you try to update HourPerWeek and with a specific Teachers ID, you should add a parameter to your stored procedure just to indicate the Teacher_ID.

NOTE: I've used a int value change it according your table schema.

CREATE PROC spUpdateTeachingHoursAndWages (@Teacher_ID int)
AS
    UPDATE Payments
    SET    HoursPerWeek = (SELECT COUNT(PupilDetails.Teacher_ID)
                           FROM   Assignment.dbo.Payments 
                           JOIN   Assignment.dbo.PupilDetails 
                           ON     Payments.ID = PupilDetails.Teacher_ID
                           WHERE  Payments.ID = @Teacher_id)
    WHERE Payments.ID = @Teacher_ID

Upvotes: 2

Related Questions