CODEWITHSUNDEEP
sqlsql-server
Rose
Rose

Reputation: 363

SQL set column to row count

I'm using a SQL Server database and have two tables with the following columns:

Driver: DriverID, NumCars

Cars: CarID, DriverID

NumCars is empty, but I need it to be the count of rows in Cars that contain that particular DriverID. How would I do this?

Upvotes: 2

Views: 714

Answers (7)

Erran Morad
Erran Morad

Reputation: 4743

Try this query. It works.

update Driver
set Driver.Numcars = src.cnt
from
(
select c.DriverId, count(c.CarId) as cnt
from Cars as c
inner join Driver as d
on c.DriverId = d.DriverId
group by c.DriverId
) as src
where Driver.DriverId = src.DriverId

Upvotes: 0

arserbin3
arserbin3

Reputation: 6148

You can fetch the count of Cars that belong to a driver, along with all Driver data with the following SELECT query:

SELECT *
    ,(
        SELECT COUNT(*)
        FROM Cars c
        WHERE c.DriverID = d.DriverID
        )
FROM Driver d

You can UPDATE the NumCars column with the following statement:

UPDATE Driver
SET NumCars = (
    SELECT COUNT(*)
    FROM Cars
    WHERE Driver.DriverID = Cars.DriverID
    )

Upvotes: 0

db_brad
db_brad

Reputation: 923

I would do a subquery, then JOIN that to the table you want to update. 

UPDATE  d
SET     NumCars = [NumCars]
FROM    Driver AS d
        INNER JOIN (SELECT  d.DriverID, COUNT(c.CarID) AS NumCars
                FROM    Drivers AS d
                        INNER JOIN Cars AS c ON c.DriverID = d.DriverID
                GROUP BY d.DriverID 
                ) AS Sub ON sub.DriverID = a.DriverID

Upvotes: 0

user2915926
user2915926

Reputation: 1

If you want the updation of the column to be dynamic, create an after insert/update trigger on the second table and inside that trigger, you can update the first table's column by using any one of the above mentioned approaches.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69514

UPDATE D
  SET D.NumCars = C.NumCars
FROM Driver D INNER JOIN (SELECT DriverID, COUNT(*) AS NumCars
                          FROM Cars
                          GROUP BY DriverID) C
ON D.DriverID = C.DriverID

Upvotes: 1

Luke
Luke

Reputation: 513

This will set all entries of the NumCars column in the Driver table to the number of cars associated with that driver:

UPDATE Driver SET NumCars = (SELECT COUNT(*) FROM Cars WHERE Driver.DriverID = Cars.DriverID);

Upvotes: 0

durbnpoisn
durbnpoisn

Reputation: 4669

Try:

SELECT count(carID) as CountOfCars from Cars where DriverID = [DriverID]

Will give you the count based on input DriverID or

SELECT DriverId, count(carID) as CountOfCars from Cars group by DriverID

Will give you all counts of carIDs and group them by DriverID

If you need to base the counts on the data from the Driver table:

SELECT count(carID) as CountOfCars from Cars inner join on Driver.DriverID = Cars.DriverID group by Cars.DriverID

Upvotes: 1

Related Questions