SQL Server : mise à jour + comptage pour chaque colonne

Question

J'essaie de mettre à jour ma base de données. Mon USER possède une colonne NbFollower qui doit contenir le nombre de personnes qui suivent cet utilisateur.

Je dois donc compter ce nombre à partir d'un autre tableau FOLLOWING_USER .

Le USER.UserID = 1 apparaît 4 fois dans FOLLOWING_USER . Cela signifie qu'il a 4 adeptes.

Comment puis-je utiliser la fonction UPDATE et un COUNT en même temps, pour chaque personne ?

UPDATE [USER]
SET NbFollower = (SELECT count(User)
                  FROM FOLLOWING_USER FU
                  INNER JOIN [USER] U ON FU.[User] = U.UserID)

Cela renvoie "13" pour TOUS mes utilisateurs (13 est le nombre total de lignes dans le fichier FOLLOWING_USER )

J'ai donc besoin de quelque chose comme ça, mais automatisé pour tous mes utilisateurs :

SELECT COUNT([User]) 
FROM FOLLOWING_USER 
WHERE [User] = 1

EDIT : voici ma table USER

CREATE TABLE [USER]
(
    UserID int IDENTITY(1, 1) PRIMARY KEY,
    URL varchar(50) NOT NULL,
    Name varchar(20) NOT NULL,
    Password varchar(50) NOT NULL,
    Email varchar(100) NOT NULL,
    Age int NOT NULL,
    Gender char(1) NOT NULL,
    Location varchar(50) NOT NULL,
    Description varchar(200) NULL,
    NbFollower int NULL DEFAULT 0,
    Rating int NULL DEFAULT 0
);

ALTER TABLE [USER]
    ADD ShareoutwardzID INT 
        FOREIGN KEY REFERENCES SHAREOUTWARDZ(ShareoutwardzID),
        CONSTRAINT Age CHECK (Age>=18);

INSERT INTO [USER] (Name, URL, Password, Email, Age, Gender, Location, Description, Rating)
VALUES
('lily', '/NAME', 'azertyuop123', 'lily@hotmail.com', 21, 'F', 'USA', NULL, 4);

Voici ma table FOLLOWING_USER :

CREATE TABLE FOLLOWING_USER
(
    [User] int NOT NULL FOREIGN KEY REFERENCES [USER](UserID),
    Follower int NOT NULL FOREIGN KEY REFERENCES FOLLOWER(UserID),
);

INSERT INTO FOLLOWING_USER([User], Follower)
VALUES (1, 9), (2, 9), (4, 9), (6, 9);

Answer 1

Vous y étiez presque, vous pouvez utiliser le [user].user_id pour ne compter que le nombre correct.

Et vous devez utiliser COUNT(*)

CREATE tABLE SHAREOUTWARDZ (ShareoutwardzID int PRIMARY KEY)
GO

CREATE TABLE [USER]
(
    UserID int IDENTITY(1, 1) PRIMARY KEY,
    URL varchar(50) NOT NULL,
    Name varchar(20) NOT NULL,
    Password varchar(50) NOT NULL,
    Email varchar(100) NOT NULL,
    Age int NOT NULL,
    Gender char(1) NOT NULL,
    Location varchar(50) NOT NULL,
    Description varchar(200) NULL,
    NbFollower int NULL DEFAULT 0,
    Rating int NULL DEFAULT 0
);
ALTER TABLE [USER]
    ADD ShareoutwardzID int FOREIGN KEY REFERENCES SHAREOUTWARDZ(ShareoutwardzID),
    CONSTRAINT Age CHECK (Age>=18);
INSERT INTO [USER](Name, URL, Password, Email, Age, Gender, Location, Description, Rating)
VALUES
('lily', '/NAME', 'azertyuop123', 'lily@hotmail.com', 21, 'F', 'USA', NULL, 4),
('MArk', '/NAME', 'azertyuop123', 'mark@hotmail.com', 21, 'M', 'USA', NULL, 4),
('Mary', '/NAME', 'azertyuop123', 'Mary@hotmail.com', 21, 'F', 'USA', NULL, 4),
('David', '/NAME', 'azertyuop123', 'david@hotmail.com', 21, 'M', 'USA', NULL, 4)
;
GO

CREATE TABLE FOLLOWER(UserID int PRIMARY KEY);
INSERT FOLLOWER VALUES (9)
GO

CREATE TABLE FOLLOWING_USER
(
    [User] int NOT NULL FOREIGN KEY REFERENCES [USER](UserID),
    Follower int NOT NULL FOREIGN KEY REFERENCES FOLLOWER(UserID),
);
INSERT INTO FOLLOWING_USER([User], Follower)
VALUES
(1, 9),
(2, 9),
(2, 9),
(3, 9);
GO

    UPDATE [USER]
SET NbFollower = (SELECT count(*)
                  FROM FOLLOWING_USER FU

                  WHERE FU.[User] = [USER].UserID)
GO

SELECT * FROM [USER]
GO

UserID | URL   | Name  | Password     | Email             | Age | Gender | Location | Description | NbFollower | Rating | ShareoutwardzID
-----: | :---- | :---- | :----------- | :---------------- | --: | :----- | :------- | :---------- | ---------: | -----: | --------------:
     1 | /NAME | lily  | azertyuop123 | lily@hotmail.com  |  21 | F      | USA      | _null_        |          1 |      4 |            _null_
     2 | /NAME | MArk  | azertyuop123 | mark@hotmail.com  |  21 | M      | USA      | _null_        |          2 |      4 |            _null_
     3 | /NAME | Mary  | azertyuop123 | Mary@hotmail.com  |  21 | F      | USA      | _null_        |          1 |      4 |            _null_
     4 | /NAME | David | azertyuop123 | david@hotmail.com |  21 | M      | USA      | _null_        |          0 |      4 |            _null_

_db<>fiddle aquí_

Answer 2

UPDATE [USER]
SET NbFollower = UserCount.TotalCount
                 FROM [USER]
                 INNER JOIN (SELECT COUNT(User) TotalCount, User 
                             FROM [Following_User] GROUP BY User) As UserCount
                 ON UserCount.User = [USER].UserId