How to DELETE a table, then INSERT in PLSQL by SELECT * plus an additional column

Question

I wish to delete the contents of a table, populate it with the contents from a very lengthy query, and also add another column of data.

My query and the table use identical field names, but the table has an additional column called LAST_UPDATED. This column needs to be updated with SYSDATE for each row.

My code thus far looks like this:

BEGIN
    delete from TABLE;
    insert into TABLE
    SELECT * from
        (Here there is about a thousand lines of code, nested selects, joins, etc);
    commit;
END;

So when I run it right now, I get an error that there are not enough values. How do I explicitly set the value of the LAST_UPDATED column to SYSDATE?

(I have tested the query as a script in TOAD on its own (without the delete and insert) and it returns the desired values to the Script Output (minus the LAST_UPDATED column)).

change the table definition to include the new column you are selecting. as it stands, the question is unclear — Vamsi Prabhala, Oct 28 '15 at 16:29
I'm not selecting a new column. The table I'm populating already has the column called LAST_UPDATED. I need to populate the table with the data from my query (which returns all the other values already) and also populate the contents of the LAST_UPDATED column with SYSDATE. — QuestionMarks, Oct 28 '15 at 16:32
does the query contain the last_updated column, or is that missing? Also, is this an ad-hoc query, or is this something that's going to be run regularly? — Boneist, Oct 28 '15 at 17:04
Use `EXECUTE IMMEDIATE 'TRUNCATE TABLE '` instead of `DELETE FROM ` unless you need the deletes to be logged. Much less overhead. Also, its bad practice to use `select *` in production. Your code will break if columns get added. — Gary_W, Oct 28 '15 at 17:45

score 2 · Accepted Answer · answered Oct 28 '15 at 17:20

If this process is for an ad-hoc, non-production purge+reload, AND the query does not return the last_updated column, then you could do something like this:

BEGIN
    delete from TABLE;
    insert into TABLE
    SELECT x.*, sysdate last_updated from
        (Here there is about a thousand lines of code, nested selects, joins, etc) x;
    commit;
END;
/

However, this is very risky, since if the columns of your query don't match the columns of the table (or if someone adds a column to your table unbeknownst to you), you could run into problems.

Far better would be to explicitly state the names of the columns - most especially if this is something that's going to be running in production - like so:

BEGIN
    delete from TABLE;
    insert into TABLE (col1, col2, col3, ... , last_updated)
    SELECT col1,
           col2,
           col3,
           ...,
           sysdate last_updated
    from
        (Here there is about a thousand lines of code, nested selects, joins, etc) x;
    commit;
END;
/

This way, you are much less likely to run into any bugs due to re-ordering of table columns, etc etc.

How to DELETE a table, then INSERT in PLSQL by SELECT * plus an additional column

1 Answers1