CODEWITHSUNDEEP
sql-server
user3673417
user3673417

Reputation: 189

SQL Server Count

I have a situation where I need to count the number of records (Lines) for each job number in the jobtable. I have the following select script which does this.

Select job, COUNT (*) AS 'Lines'
FROM jobtable
GROUP BY job


Job       Lines
--------- --------
1         26

2         12

3         46
...

Now I would like to update a second table "materials" using the result of the count in a column called 'time'. So in the table called materials where the job = 1 update time = 26, where job is = 2 update the time = 12 etc ......

Upvotes: 0

Views: 80

Answers (2)

Joe Farrell
Joe Farrell

Reputation: 3542

In addition to the answer provided by Rahul, if you're on SQL Server 2005 or later, you can use a CTE, which I personally find more readily understandable than constructions using subqueries:

with CountsCTE as
(
    select [job], count(*) as [lines]
    from [jobtable]
    group by [job]
)
update [materials]
set [time] = C.[lines]
from [materials] M
    inner join [CountsCTE] C on M.[job] = C.[job];

If you're on SQL Server 2008 or later, you can replace the UPDATE with a MERGE:

with CountsCTE as
(
    select [job], count(*) as [lines]
    from [jobtable]
    group by [job]
)
merge into [materials] M
    using [CountsCTE] C on M.[job] = C.[job]
    when matched then update set M.[time] = C.[lines];

Upvotes: 0

Rahul
Rahul

Reputation: 77866

You can try like below, joining your materials table with your current query result

UPDATE
    m
SET
    [time] = lines
FROM
    materials m
    JOIN
(
Select job, COUNT (*) AS 'Lines'
FROM jobtable
GROUP BY job
) tab on m.job = tab.job

Upvotes: 1

Related Questions