Simple SQL Query to ADD to table?

Question

There a table named "topic" which has certain fields "Number", "Title", "essay".

Now what I want to do is add new members in the table "topic" in such a way that the "number" increases to a number which I specify (say 100) while the "Title" and "essay" remains the same.
Example:-
Originally, the table "topic" has
Number Title Essay
1 t1 e1
2 t2 e2

Add, so that the table has:
Number Title Essay
1 t1 e1
2 t2 e2
3 t1 e1
4 t2 e2
5 t1 e1
6 t2 e2
.
.
.
Till a number I specify.

Well, I am just a student very new to SQL, so I may have used wrong words for explaining.
Can some give me the SQL query in basic clauses (if possible) like UPDATE, SET etc to run for this?

Answer 1

You're looking for INSERT...



I think you would have to combine with another language to do the looping mechanism you require, but I could be wrong... It's just that I've never seen someone do what you put here with SQL alone...

Answer 2

You could do this interactively by just

1) INSERT INTO topic FROM

(SELECT number + rowCount, title, essay FROM topic,

(SELECT COUNT(*) AS rowCount FROM topic))

2) SELECT MAX(number) FROM topic

3) If result in 2 < 100, go to 1 and repeat

4) DELETE FROM topic WHERE number > 100



The easiest way to do this non-interactively is via a script (T-SQL, PL/SQL or whatever is appropriate for your DBMS) with SQL embedded in it. If you know the existing rows are contiguous and start at 1 (as in your example)



1) Get maxValue desired from user (100 in this case)

2) SELECT COUNT(*) FROM topic INTO curRowCount

3) numInserts = 0, target = maxValue

4) Until target = 0;

a) numInserts = numInserts + 1

b) target = target / curRowCount

5) For i = 1->numInserts

a) INSERT INTO topic FROM (SELECT number + curRowCount, title, essay)

b) curRowCount = curRowCount * 2

6) DELETE FROM topic WHERE number > maxValue

Answer 3

If you're using Oracle, you can try something like: SELECT SUM (MARK) AS SUM_OF_MARKS FROM databasename WHERE MARK > minvalue AND MARK < maxvalue; sum_of_marks can be replaced with anything - that would give you the column head for the answers. minvalue will be the low end of your interval. maxvalue will be your high end of the interval. Hope it helps, Alex.