Oracle updates the data of different table structures and fields to another table, and then inserts it into the new table

Oracle Update and insert operations of different tables

In production , A table due to configuration problems A There is no data in one of the fields , Result in B The data in the table is completely null.

The current requirements are as follows ： With the help of g_wo_sn C The data of serial_number, Then update the data to G_SN_IMEI_MANAGE A Tabular SN Column , The structure of the two tables is inconsistent , Then insert into C surface

namely ： hold C Of serial_number Update to A Tabular SN

C The table structure is as follows

select a.*,rownum from g_wo_sn a where A.WORK_ORDER='WORK015903' and rownum<=9000;

B The table structure is as follows

select a.*,rownum from G_SN_IMEI_MANAGE a where a.WORK_ORDER='WORK015903' and rownum<=9000;

because C Table and A The table structure of the table is different , There is no same row data . But watch carefully A Table fields , We found that A Tabular NOT_BURN_IMEI This column has no data ,name You can use Oracle Built in functions for rownum Update data . Steps are as follows ：

1. Update first A Tabular NOT_BURN_IMEI Field , Let it be arranged in numbers . namely NOT_BURN_IMEI=rownum. This is the most important step

update G_SN_IMEI_MANAGE set NOT_BURN_IMEI=rownum where WORK_ORDER='WORK015903';

2. After the C Tabular serial_number Update to A Tabular SN in

update G_SN_IMEI_MANAGE a set a.sn=(select t.serial_number from (select a.*,rownum  as g from g_wo_sn a where A.WORK_ORDER='WORK015903' and rownum<=9000)t where t.g=a.NOT_BURN_IMEI) where a.work_order='WORK015903';

3. The last update A Tabular NOT_BURN_IMEI Field

update G_SN_IMEI_MANAGE set NOT_BURN_IMEI=null where WORK_ORDER='WORK015903';

4. Then insert into B In the table

insert into IMEI_SN_K1807 b (WORK_ORDER,PART_ID,SN,IMEI,UPDATE_USERID,UPDATE_TIME,FLAG) 
(select WORK_ORDER,PART_ID,SN,IMEI,OPERATOR_ID,OPERATOR_TIME,FLG from G_SN_IMEI_MANAGE a where a.WORK_ORDER='WORK015903') ;

Of course , We can also use Excle File import . But when it comes to different data volumes of two tables , You can try rownum function , It's much easier to use than you think .