| sql to copy records [message #41086] |
Fri, 22 November 2002 07:22  |
jim anderson
Messages: 5
Registered: November 2002
|
Junior Member |
|
|
I need to copy some records and change a field value
with one sql statement in the same table.
insert into table1 select * from table1
I have too many rows and different tables to work with.
I don't want to list the columns or use temp tables,
the above works but you end up with duplicates i need to use some sql that will do the above but at the same time change a certain field value. can anyone help? thanks in advance
|
|
|
|
| Re: sql to copy records [message #41087 is a reply to message #41086] |
Fri, 22 November 2002 08:10  |
B
Messages: 327
Registered: August 1999
|
Senior Member |
|
|
I'm not sure i understand ...
but
insert into [[ TABLE ]]
select decode( [[col]], [[value1]],[[Replace1]], .... ) from [[ SOURCE_TABLE ]]
|
|
|
|
| Re: sql to copy records [message #41089 is a reply to message #41086] |
Fri, 22 November 2002 09:04 |
jim anderson
Messages: 5
Registered: November 2002
|
Junior Member |
|
|
Hi thanks i just want to copy all the records in a table and insert them into the same table but change
a column so there will be no duplicates.
I can't seem to get the above to do that?
thanks
jim anderson
|
|
|
|
| Re: sql to copy records [message #41092 is a reply to message #41086] |
Fri, 22 November 2002 12:44 |
GDesai
Messages: 1
Registered: November 2002
|
Junior Member |
|
|
You could have a statement like
Lets say you have a table with columns : col1 col2 and col3 and you need to update col3, to value which is col3*100
You could use
insert into table1 select col1,col2,col3*100 from table1
in conjunction with a before insert trigger on each row which checks if a record with a similar values exist for all other columns (col1,col2) and deletes that record.
one question : why cant you use an update statement?
|
|
|
|
| Re: sql to copy records [message #41094 is a reply to message #41092] |
Fri, 22 November 2002 13:20 |
jim anderson
Messages: 5
Registered: November 2002
|
Junior Member |
|
|
HI thanks.
The the number of columns and tables are large. I do
not want to list the colmn names. I want to use
insert into table1 select * from table1
but change just one field value so there is no duplicate.
|
|
|
|
|
|
|
|
| Re: sql to copy records [message #41151 is a reply to message #41092] |
Mon, 02 December 2002 09:03 |
jim anderson
Messages: 5
Registered: November 2002
|
Junior Member |
|
|
Hi thanks have a large number of tables and columns than will be changing I don't want to update the code.
Your solution will work but I need one where you don't
have to list the columns. Is this possible?
I just want to copy a record and change a col1 value.
insert into table1 from table1 where col1 = 100
then change col1 to 200 somehow to avoid a duplicate
record.
thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
