CODEWITHSUNDEEP
sqlsql-serverstored-procedures
mechalaris
mechalaris

Reputation: 161

SQL Server: Stored procedure to update field using count

I have never written a stored procedure before. How would i go about updating a field in Table A with the results of a count of records in Table B that share a key?

Table A is a "users" table with a Primary key "userid" and a field "rentals_count". Table B is a "rentals" table with a Foreign Key "userid"

For each user (record) in Table A update the "rentals_count" field with the sum of rentals in Table B that match that user as an integer.

The question includes the mechanics of actually implementing and running the stored procedure on a nightly basis.

Upvotes: 0

Views: 1098

Answers (1)

John Woo
John Woo

Reputation: 263933

For SQL Server

CREATE PROCEDURE UpdateTableA
AS
UPDATE  a
SET     a.rentals_count = b.totalCount
FROM    TableA a
        INNER JOIN
        (
            SELECT  userID, Count(*) totalCount
            FROM    TableB
            GROUP   BY userID
        ) b ON a.userID = b.userID

For MySQL

UPDATE  TableA a
        INNER JOIN
        (
            SELECT  userID, Count(*) totalCount
            FROM    TableB
            GROUP   BY userID
        ) b ON a.userID = b.userID
SET     a.rentals_count = b.totalCount

Upvotes: 2

Related Questions