将数据从一个表更新到另一个表(在数据库中) [英] update data from one table to another (in a database)
问题描述
DB gurus,
我希望有人能设定正确的方向。
I am hoping someone can set set me on the right direction.
有两个表。表A和表B.当系统启动时,来自表A的所有条目被按摩并复制到表B(根据表B的模式)。表A可以有数万行。
I have two tables. Table A and Table B. When the system comes up, all entries from Table A are massaged and copied over to Table B (according to Table B's schema). Table A can have tens of thousands of rows.
系统启动时,表B通过DB更改通知与表A保持同步。
While the system is up, Table B is kept in sync with Table A via DB change notifications.
如果系统重新启动或服务重新启动,我想重新初始化表B.但是,我想这样做与最少可能的数据库更新。具体来说,我想:
If the system is rebooted, or my service restarted, I want to re-initialize Table B. However, I want to do this with the least possible DB updates. Specifically, I want to:
- 添加表A中的所有行,而不是表B中的行,
- 删除不在表A中但在表B中的任何行
- 表A和表B通用的任何行都应保持不变
现在,我不是一个DB人,所以我想知道什么是常规的做法。
Now, I am not a "DB guy", so I am wondering what is conventional way of doing this.
推荐答案
使用存在
将处理保持到最低。
Use exists
to keep processing to a minimum.
沿着这些线,修改所以连接是正确的(也验证我没有做一些愚蠢,得到 TableA
和 TableB
从您的描述向后):
Something along these lines, modified so the joins are correct (also verify that I didn't do something stupid and get TableA
and TableB
backwards from your description):
insert into TableB
select
*
from
TableA a
where
not exists (select 1 from TableB b where b.ID = a.ID)
delete from
TableB b
where
not exists (select 1 from TableA a where a.ID = b.ID)
这篇关于将数据从一个表更新到另一个表(在数据库中)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!