复制具有insert select的记录，然后更新同一组记录中的属性 [英] Copy records with insert select, then update attributes in the same set of records

问题描述

我有一个Oracle程序，其中我想执行以下操作：

1. 将记录从一个表复制到另一个表


2. 更新原始记录以表示它们已被复制

目前我使用FOR循环单独处理每个记录。

我想：

这是一个很好的代码，

1. 删除for循环并使用INSERT INTO ... SELECT ...语句复制记录


2. 如果




这看起来很简单，只有一个INSERT和UPDATE语句具有相同的WHERE子句。然而，在执行两个语句之间的时间，可能插入更多的需要复制的记录。如果我使用相同的WHERE子句，我可以将记录标记为已处理的记录，但没有实际处理。





有一种方法可以保存主键列表在两个语句中使用还是合并语句？或者你会推荐我坚持使用FOR循环并一次处理一个记录？所有建设性输入欢迎。

解决方案

如果在事务中运行 code> ，任何失败都会导致插入和更新被回滚。

  insert into tgt .. 
 select * from src 
其中< condition> 
和row_status<> 'copy'; - 信息记录复制一次后？ 
 
 update src 
 set row_status ='copied'
其中< same_where_condition_as_before> 
 
 commit; 
  

如果在读取之后在源表中插入新行，您可能需要运行块，再次修改，如果合适，更改的条件。




I have an Oracle procedure in which I would like to do the following:

1. Copy records from one table to another
2. Update original records to indicate they have been copied

Currently, I am using a FOR loop to process each record individually. This works, but it is a lot of code to do something I think could be simpler.

I would like to:

1. Eliminate the for loop and copy the records with an INSERT INTO ... SELECT ... statement
2. If copy is successful, update all selected records.

This may seem simple, just one INSERT and an UPDATE statement with the same WHERE clause. However, in the time between execution of the two statements, more records may be inserted that need to be copied. If I use the same WHERE clause, I may mark records as processed that have not actually been processed.

Is there a way I can save a list of the Primary Keys for use in both statements or merge the statements? Or would you recommend I stick with the FOR loop and process the records one at a time? All constructive input welcome.

解决方案

If you run the two statements within a transaction, any failures will cause both the insert and the update to be rolled back.

insert into tgt..
select * from src
where <condition>
  and row_status <> 'copied'; --ignoring records after they have been copied once?

update src
set row_status = 'copied'
where <same_where_condition_as_before>

commit;

If there are new rows inserted in the source table after they have been read, you might need to run the block again, with a changed where condition if appropriate.

这篇关于复制具有insert select的记录，然后更新同一组记录中的属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！