I need to add a new row to a table in Oracle. The problem is that the table has 50 columns and I really don't want to write them all out for an INSERT statement. I tried to do a SELECT INTO statement to duplicate the row and then change the fields I care about individually, but this results in a UNIQUE violation on the primary key. So what I really want to do is declare a variable that holds one row without naming all the columns, change the primary key field, and then insert that variable. How do?
2 Answers
You can use %ROWTYPE in an anonymous PL/SQL block to declare a record representing a row from a table and then select a row into that record and change the primary key and insert the updated record. You can even re-use it for multiple inserts:
DECLARE
rec SOME_TABLE%ROWTYPE;
BEGIN
SELECT *
INTO rec
FROM SOME_TABLE
WHERE A = 1; -- Primary Key
rec.A := 2; -- Change the primary key value.
INSERT INTO SOME_TABLE VALUES rec;
rec.A := 3; -- Change the primary key again.
INSERT INTO SOME_TABLE VALUES rec;
FOR i IN 4 .. 9 LOOP
rec.A := i; -- Change it repeatedly...
INSERT INTO SOME_TABLE VALUES rec;
END LOOP;
FOR i IN 1 .. 3 LOOP
rec.A := SOME_SEQUENCE.NEXTVAL; -- Or you can manage the primary key's value using a sequence.
INSERT INTO SOME_TABLE VALUES rec;
END LOOP;
END;
/
Comments
I have often wanted to do something similar to this, but it's just not possible in any SQL variant I know of. You cannot ask for only some of the columns in a table without explicitly naming them (or perhaps defining a view on them in advance).
The only shortcut I can suggest is to dump the list of column names into a convenient location and then just copy it into an insert statement, changing only the value you need:
insert into foo (select 'newC1' as c1, c2, c3, c4, ..., c50 from foo where bar='baz');
::edit:: In fact, I do this so often that I wrote a Python script to help me. I tell it what table I'm editing, some where clause that matches exactly 1 row, the list of column(s) I want to change, and the list of new value(s) I want in those columns. Then it does the rest.
Comments
SELECT *sinse fields that areUNIQUEwill have to be changed in order to work. You will have to specify the field to be selected and provide the information to be inserted in the others that are not being replicated. If theUNIQUEis also aAUTO_INCREMENT, use the valueNULLto make it complete automatically.AUTO_INCREMENT; that is MySQL syntax. To get incrementing values in Oracle you need to use a sequence.