Making a new column and setting default values equal to the COUNT of a certain condition in SQL? [on hold]

That one-liner is hard to read!

ALTER TABLE table1 
   ADD counter INT DEFAULT (SELECT COUNT(table2.car_type) 
                            FROM table2 
                            WHERE (table1.car_type = table2.car_type));

Oracle is a bit foreign to me, but if I read this correctly you're adding am int column called counter to a table called table1, with a default constraint that you'd like to be somewhat dynamic (I never tried that, but my guts agree with your doubts).

I'd script that in two operations - one to drop/create the table column, another to drop/create the default constraint.

However I don't think a constraint is the right approach for this - it's not a default value you want in that table, it's an automatic value. When a new toyota is added in table2 you'll want that counter in table1 to remain accurate!

I think such a column isn't useful (if you need the count of toyota cars, why not count them when you need to and get an accurate figure every time?), but if you really really really need to maintain such a counter then I'd resort to a trigger (I think triggers should be avoided): when a record is added or removed in table2, the corresponding counter in table1 should be updated with the new count.

If you are using Oracle, I would expect it would be complaining about ambiguous columns, as carName exists in two tables. Adding aliases is a good idea:

counter = SELECT COUNT(t2.CARNAME) 
          FROM Table2 t2
          JOIN Table1 t1 ON t2.CarName = t1.CarName

But why are you doing this? If the application needs an up-to-date car count, it can issue the above query on demand. If reporting users need this info, give them a view with a computed column for `carCount. It is never a good idea to record data in multiple places - it invariably gets out of date.

 ALTER TABLE table1 
 ADD counter INT DEFAULT 
     (Select COUNT(table2.car_type) 
        FROM table2 
        WHERE (table1.car_type = table2.car_type)
      );

That looks like one Query to me, have you run it in the database of your choice?

I don't think I would do it this way personally because I don't know what number I am giving to what record. the records can be in any order.

I would do it with two queries

ALTER TABLE Table1 ADD counter INT NULL

And then I would add the numbers

UPDATE Table1
SET counter = SELECT COUNT(CARNAME) 
              FROM Table2 
              JOIN Table1 ON Table2.CarName = Table2.CarName

For some reason I am fighting with my self about whether or not this will give you the right answer.