If you have a query with a WHERE clause that can extract only the row
you want to update and put back, you could try this:
- CREATE TABLE table2 AS
(SELECT * FROM table1
WHERE ........);
- UPDATE table2
SET field37=..., field98=...., etc.;
COMMIT WORK;
- INSERT INTO table1
SELECT * FROM table2;
COMMIT WORK;
- DROP TABLE table2; (or you may want only to DELETE/TRUNCATE table2,
if you intend to go through this excercise again).
However, no matter which solution you are going to implement, I see a
problem when inserting the modified row back into table1, i.e. primary
key violation, since (that's my understanding) you intend to keep the
original row (unless you modify the primary key too, but this is not
clear to me from your posting).
Hope this helps.
Michael Serbanescu
Anders Blaagaard wrote:
>
> I have a table with more than 100 columns, and a want to make a copy of
> a row, change a few fields, a put it back into the table (without
> changing the original). The idea is this:
>
> declare
> tmp_row table_name%rowtype;
> begin
> select * into tmp_row from table_name where ...
> tmp_row.field37 := ..
> tmp_row.field98 := ..
> insert into table_name values (tmp_row); -- ILLEGAL
> end;
>
> Can anyone think of an easy way to do this?
>
> Anders Blaagaard
> blaa_at_ifad.dk
Received on Wed Sep 24 1997 - 00:00:00 CDT