Is there a way, without typing in all the field names to update an entire table row from a query?

One table in-memory created using sql and a load of joins and what I want to do is use the same base query to produce a new row and replace the row in the in-memory table with the latest data.

Roy Lambert

Roy Lambert wrote:

> Is there a way, without typing in all the field names to update an
> entire table row from a query?
>
> One table in-memory created using sql and a load of joins and what I
> want to do is use the same base query to produce a new row and
> replace the row in the in-memory table with the latest data.
>
> Roy Lambert

"produce a new row"  and "replace the row" are two separate ideas.
You'd have to delete the row then insert the new one if you don't want
to type all the fields.  But if you want to keep it to one statement
(UPDATE), I don't see anyway around typing in all the fields.


SCRIPT
 EXECUTE IMMEDIATE 'DELETE FROM MyTable2 WHERE ID = 123';
 EXECUTE IMMEDIATE 'INSERT INTO MyTable2
               SELECT * FROM MyTable1 WHERE ID = 123';
END;

Of course, to do this, the field types and positions (and names?) have
to match exactly.


I've implemented a "logging" concept where the log table had all the
exact same fields as the table being copied from but with one
additional field (a date stamp) at the end.  The "InsertLog" statement
was like this:

 INSERT INTO LogTable SELECT *, CURRENT_DATE FROM OriginalTable

.... and of course there was a WHERE clause to only select the new
records.

Anyway, this is probably more than what you asked for, but I thought it
might help someone who doesn't realize they can do this.

--
David Cornelius
CorneliusConcepts.com

Roy,

<< Is there a way, without typing in all the field names to update an entire
table row from a query? >>

Only in the fashion that David indicated - delete the row and re-insert the
new row.

--
Tim Young
Elevate Software
www.elevatesoft.com

David


Of course I know I can DELETE a row and then INSERT a row, but for some reason such a simple, easy and "obvious" solution never even fluttered across my mind.

As they say in all the best BarclayCard ads "that will do nicely sir".

Thanks

Roy Lambert

> Of course I know I can DELETE a row and then INSERT a row, but for
> some reason such a simple, easy and "obvious" solution never even
> fluttered across my mind.

http://seeminglyrandom.wordpress.com/2006/09/16/murphys-law-the-third-sn
afu-equation/



--
David Cornelius
CorneliusConcepts.com

David


Nice version of Murphy's Law. However, I still prefer "the perversity of the universe tends to a maximum"

Roy Lambert