Software

Ask something
Discussion Locked

Oracle SQL help

0 helpful
2 replies

by blarman 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm struggling with a query. I need to copy some data from a similar table (unused) into a current (in use) table. I need two fields worth of data, and I specifically need them to get the info relative to the current ID.

I have been trying to write a dynamic update statement, but keep getting stuck. The field names are the same in both tables, and the ID's match exactly between tables.

Any ideas?

2 replies

by aniljkumar

I am not sure whether I have understood your requirement. But it is worth starting some where. Say you have two tables old and new. You want to update the new one with the old one's data. Your current id = 1. The update statement would be like this.
update (select old.col1,old.col2,new.col1,new.col2
from old, new where new.id = old.id and new.id = 1)
set old.col1 = new.col1,
old.col2 = new.col2;

You can swap the updates (update new or old) in this statement. Please let me know is this what you are looking for? Email me in detail what you are looking for if this does not solve your problem.

by okda7

at first if the Activityx table is the unused table which contains the data and contain the following column
un_code,
sec_code,
act_code,
activity

which is similar to the second table which is called activity (used)
select from first table activitx and insert it into the other table activity

1- first do this select statement on the table
select 'insert into activity values ('''||un_code||''','
||''''||sec_code||''','||''''||act_code||''','||''''||activity||''');'
from activityx;
------------------------------------
the result will be as follows:

insert into activity values ('3','1','11','Demo Plots');
insert into activity values ('3','1','12','Field Day');
insert into activity values ('3','1','13','Follow Up Research');
insert into activity values ('3','1','14','Follow Up Staff');
insert into activity values ('3','1','15','Contact Farmers');
insert into activity values ('3','1','16','S.M.S');
insert into activity values ('3','1','17','Extension Meeting');
insert into activity values ('3','1','18','Cropping Pattern');
insert into activity values ('3','3','33','Informative Meeting');
insert into activity values ('3','2','21','Extension Demonestration Plot');
insert into activity values ('3','2','22','Field Day');
insert into activity values ('3','2','23','Follow Up Research');
insert into activity values ('3','2','24','Follow Up Staff');
----
2- the result of statement from the screen is the code to be used to insert to other table
simply spool the result of query to sql file

This discussion is closed to new replies.