CODEWITHSUNDEEP
sqlsql-servercount
Aspiring Developer
Aspiring Developer

Reputation: 680

Update column with count of row value in another column in SQL Server

I have no idea why I haven't found a solution to this on here but: I am trying to COUNT how many times the Serial_Parent appears during the packing phase so I can use this in other calculations.

This works perfectly:

SELECT 
    Serial_Parent, COUNT(Serial_Parent)
FROM 
    mfng_data
WHERE 
    Machine_Process = 'Packing'
GROUP BY 
    Serial_Parent;

However when I try to UPDATE a column with this COUNT, I fail miserably as it just counts all the rows in the table and saves that as each row value thus 2,134,222 appearing in each row value.

I've tried this:

UPDATE mfng_data
SET Count_Serial_Parent = (SELECT COUNT(*)
                           FROM mfng_data 
                           WHERE Machine_Process = 'Packing'
                             AND Serial_Parent = Serial_Parent) 
WHERE Serial_Parent = Serial_Parent;

I've also tried this:

UPDATE mfng_data
SET Count_Serial_Parent = (SELECT COUNT(Serial_Parent)
                           FROM mfng_data 
                           WHERE Machine_Process = 'Packing'
                             AND Serial_Parent = Serial_Parent);

Sample data:

Spec: 12373  Rev: -6    M35846  M358461 M3584610    M35846101   NULL    NULL    NULL    M35846101   6808
Spec: 12373  Rev: -6    M35846  M358461 M3584610    M35846102   NULL    NULL    NULL    M35846102   6808
Spec: 16692  Rev: -4    K45678  K456781 K4567810    K45678101   NULL    NULL    NULL    K45678101   3964
Spec: 16692  Rev: -4    K45678  K456782 K4567820    K45678201   NULL    NULL    NULL    K45678201   3978
Spec: 16693  Rev: -4    K45678  K456782 K4567820    K45678202   NULL    NULL    NULL    K45678202   3806

Desired result (M35846 will appear twice so it will list "2" for each row entry)

Serial_Parent Count_Serial_Parent
----------------------------------
M35846           2
M35846           2
J39384          52 - - > 52 rows will show "52" and so on below
M35488          10
K4448            4
M35927           8
K45678           3

Upvotes: 1

Views: 1538

Answers (2)

user1443098
user1443098

Reputation: 7635

Another way (using a toy table -- hope you get the idea):

DECLARE @ TABLE (Serial_Parent int, Count_Serial_Parent int)
INSERT INTO @ (Serial_Parent, Count_Serial_Parent) VALUES
(1,0), (1,0),(2,0),(2,0),(2,0)
SELECT * FROM @

UPDATE m -- mfng_data

SET m.Count_Serial_Parent = j.Count_Serial_Parent
FROM @ m -- mfng_data
JOIN (
SELECT Serial_Parent, COUNT(*) Count_Serial_Parent
FROM @ 
GROUP BY Serial_Parent
) j ON j.Serial_Parent = m.Serial_Parent

SELECT * FROM @

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You need table aliases and qualified column names:

UPDATE m
    SET Count_Serial_Parent = (SELECT COUNT(*)
                               FROM mfng_data m2
                               WHERE m2.Machine_Process = 'Packing' AND
                                     m2.Serial_Parent = m.Serial_Parent 
                              )
    FROM mfng_data m;

The subquery needs to be correlated to the outer query. A condition such as Serial_Parent = Serial_Parent basically always evaluates to true because it is referring to the column of the table referenced in the subquery.

However, the better approach is an updatable CTE:

with toupdate as (
      select m.*,
             sum(case when m.Machine_Process = 'Packing' then 1 else 0 end) over (partition by m.serial_parent) as new_Count_Serial_Parent 
      from  mfng_data m
     )
update toupdate
    set Count_Serial_Parent = new_Count_Serial_Parent;

Upvotes: 3

Related Questions