Insert into table field a result of sql query

Question

maybe this question is duplicate or my query in Google wasn't right, but I have a problem) I have a 3 tables: 1. Contact (int ID, nvarchar Name) 2. City (int ID, nvarchar Name, int ContCount) 3. Adress (int ID, int CityID, int ContactID, int Year)

ContCount field - its a number of a Contacts what are living in some City (City.Name). My query for it: SELECT COUNT(*) FROM dbo.Adresses WHERE dbo.Adresses.City_ID = 1

Here is my question: I need to my ContCount field assign a result of this query. Can you help me? How can I do it?

P.S. Sorry for my English=)

Are you trying to update the ContCount for all cities in the City table? Or do you just want the ContCount for a particular City_ID? — Shiva
– Shiva, Commented Dec 9, 2013 at 0:05
It should be updating dynamicaly for all cities after updating,inserting etc. in Adress table — Sashko Chehotsky
– Sashko Chehotsky, Commented Dec 9, 2013 at 0:20

prole92 · Accepted Answer · 2013-12-08 23:59:08Z

1

If you are using TSQL you can use the following code to save that variable:

DECLARE @ContCount as INT
SET @ContCount = (SELECT COUNT(*) FROM dbo.Adresses WHERE dbo.Adresses.City_ID = 1)

Im not quite sure what you want to do next, but you can use it in any query you write next, for example:

DECLARE @ContCount as INT
SET @ContCount = (SELECT COUNT(*) FROM dbo.Adresses WHERE dbo.Adresses.City_ID = 1)
UPDATE City SET ContCount=@ContCount Where ID=1

answered Dec 8, 2013 at 23:59

prole92

565 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sashko Chehotsky Over a year ago

Thanks a lot. I've used such code: UPDATE DBO.Cities SET ContactCount = (SELECT COUNT(*) FROM dbo.Adresses WHERE dbo.Adresses.City_ID = dbo.Cities.ID)

sfuqua · Accepted Answer · 2013-12-09 00:12:33Z

0

Or you could combine into a single statement. I've never used a CROSS APPLY in an UPDATE statement, but it ought to work:

UPDATE c SET ContCount = b.AddressCount
FROM dbo.City c 
CROSS APPLY (SELECT COUNT(1) as AddressCount
             FROM dbo.Address a 
             WHERE a.City_ID = c.City_ID) b

(optional: WHERE c.City_ID = 1)

answered Dec 9, 2013 at 0:12

sfuqua

5,8731 gold badge36 silver badges33 bronze badges

Collectives™ on Stack Overflow

Insert into table field a result of sql query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related