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)).
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.