Our rookie had a simple task on the SQL lab of the university he attends. Take a column in table A and copy it to table B, while adding a column id to B from a sequence C.
This seems like a matter of a single relatively simple SQL statement, and so thought their teacher. The solution we all thought to be simple looks like:
INSERT INTO B (ID, COL) VALUES (SELECT C.nextval, COL FROM A)
Nice and easy. This seems that Oracle only evaluates a the sequence operations in a statement once, thus when you take the next value from a sequence you will always get the same value within the statement. This will either result in a constraint violation (if you have one defined) or table A’s ID column filled with the same number.
So solutions like:
INSERT INTO B (ID, COL) VALUES (SELECT (SELECT C.nextval FROM DUAL), COL FROM A)
would not work either, as the expression is still evaluated once.
I see two solutions to this really simple problem, either create a trigger on table B for this excercise, which I consider an overkill, or use a LOOP. Oracle states on it’s documentation, that the loop content on every iteration.
The solution:
INSERT INTO B SELECT C.nextval, COL FROM A
For DB2 it works properly as well.
shows the required
and
inserts the values in the table correctly.
it’s funny lajos, I was trying to do this but always used the sytax containing the word “values”… of course it didn’t work