Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I have 2 tables. (table1, table2) Now, I would like to do something along the lines of:

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

That's the closest syntax I can come up with, but just by looking at it I know it's wrong. Please tell me how to do it in ONE SQL statement (if possible).

The goal table (with counter implemented) is something like this: (separated columns by -)

table1: pid-car_type-counter:

1-Honda-2
2-Toyota-3
3-Suzuki-1
4-Ferrari-0
5-Porsche-1

table2: pid-car_type:

1-Honda
2-Toyota
3-Porsche
4-Honda
5-Suzuki
6-Toyota
7-Toyota
share|improve this question

closed as off-topic by Mat's Mug, palacsint, MrSmith42, Vogel612, Malachi Apr 18 at 12:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions containing broken code or asking for advice about code not yet written are off-topic, as the code is not ready for review. Such questions may be suitable for Stack Overflow or Programmers. After the question has been edited to contain working code, we will consider reopening it." – Mat's Mug, palacsint, MrSmith42, Vogel612, Malachi
If this question can be reworded to fit the rules in the help center, please edit the question.

    
what flavor of SQL are we dealing with here? –  Malachi Apr 18 at 2:12
    
I'm using SQL Plus to run my scripts. So, oracle I think. –  user40128 Apr 18 at 2:18
    
I had a little doubt when I posted my answer, but with the comments it's getting clearer - you shouldn't be expecting this community to fix any issues in your code, that's Stack Overflow's job ;) ...we review working code (my answer just assumes your code works) and often come up with alternative ways or cosmetic changes, but CR isn't for changing what the code does.. actually posting non-working code is off-topic for this site, see our help center for all the details ;) –  Mat's Mug Apr 18 at 3:04
    
Oh... I assumed they were both the same. Should I delete this question then? –  user40128 Apr 18 at 3:06
1  
I don't think you can, now that there are answers... mind if I cast a close vote? It'll enter the close queue and will eventually be deleted then. (see how is this site different from stack overflow? on meta - and feel free to stick around and come back with some working code to review, too!). –  Mat's Mug Apr 18 at 3:12

3 Answers 3

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.

share|improve this answer
    
Oh, I didn't even think about if the count changed. In my case, that wont be a problem, but it's something I need to be wary of in case i do add another record. I will look it up now. Why do you think triggers should be avoided? –  user40128 Apr 18 at 2:56
1  
Because it's always the last place you think of looking for a bug at when there's a problem! These things are way too easy to forget! –  Mat's Mug Apr 18 at 2:58
    
I understand, thanks. –  user40128 Apr 18 at 3:04

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.

share|improve this answer
    
I'm doing this because I was told to. –  user40128 Apr 19 at 8:23
1  
>> I'm doing this because I was told to - Oh dear, that is a terrible reason. One of our functions in life is to influence business analysts, bosses etc when they are doing stupid things... –  kiwiron May 1 at 9:06
 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.

share|improve this answer
    
Yes, I have tried, mine says error: subquery expressions not allowed here. I have also tried your method, however, it says error line 2: missing expression (SET counter = SELECT COUNT(CARNAME)) I will try putting in a GROUP BY now. Also, I beleieve you meant "JOIN Table1 ON table1.carname = table2.carname"? –  user40128 Apr 18 at 2:42
    
The group by did not make a difference, so that isn't the problem. –  user40128 Apr 18 at 2:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.