SQL to UPDATE column from table using the COUNT result of a joined table

[TrevorPhillips]

I'm trying to update a column from table1 using the count result of joined table1 and table2.

UPDATE table1
SET column1 = (SELECT COUNT(*) FROM table1
INNER JOIN table2 ON table1.column = table2.column);

[June7]

Why are you saving aggregate data? Saving calculated data is usually a bad idea, especially aggregate data. Calculate when needed.

[TrevorPhillips]

I'm using the MS access SQL and I want to COUNT an INNER joined table and then UPDATE a field based on the result of the COUNT. How can I do that using a query or queries.

[June7]

Not sure can. Seems I have tried updating a table with an aggregate query output and get 'not updatable' error because the aggregate query is involved.

As pointed out, should not save aggregate data. Why do you feel it is necessary?

[Dal Jeanis]

Did you really want to update every individual record in table1 to have the identical count of how many matches might be made between that entire table1 and another table2? Or are you trying to update each record in table1 with the count of how many records in table2 match that particular record?

The first can be done in two steps using VBA. I just can't imagine the application that needs that.

The second can be done with a subquery, but it won't look exactly like you're showing it.

[TrevorPhillips]

Did you really want to update every individual record in table1 to have the identical count of how many matches might be made between that entire table1 and another table2? Or are you trying to update each record in table1 with the count of how many records in table2 match that particular record?

The first can be done in two steps using VBA. I just can't imagine the application that needs that.

The second can be done with a subquery, but it won't look exactly like you're showing it.

I'm trying to update every record on table1 with the count of records of table1 and table2 but forgot to put WHERE column1 = table2's foreign key?

[Dal Jeanis]

So maybe you wanted something more like this?

UPDATE table1
SET column1 = 
  (SELECT COUNT(*) 
   FROM table2 
   WHERE table1.column = table2.column);

[TrevorPhillips]

So maybe you wanted something more like this?

UPDATE table1
SET column1 = 
  (SELECT COUNT(*) 
   FROM table2 
   WHERE table1.column = table2.column);

Table1 name = flight
Table1 primarykey = flightnumber
Table1 column = seattaken

Table2 name = tickets
Table2 column = flightnumber

What I intend to do is to update all records of [seattaken] of [flight] where [flightnumber] is equal to [tickets] [flightnumber]

It seems the problem is the MS access 2007 update query not working(the database is saved as 2002-2003 format so that it will work it vb6). I tried updating a record but it doesn't change the value. Is there a way to fix it?

[Dal Jeanis]

You need to create a temp table to hold your intermediate results.
Three steps:

SELECT TT.flightnumber, Count(1) AS ticketCount INTO tempTicketCount
FROM tickets AS TT
GROUP BY TT.flightnumber;

UPDATE [flights] AS TF, [tempTicketCount] AS TT
SET TF.[seattaken] = TT.ticketCount
WHERE TF.[flightnumber] = TT.[flightnumber];

DELETE FROM tempticketcount;