从一个表中进行选择，然后根据条件插入到另外两个表中 [英] SELECT from one table, INSERT into two other tables based on condition

问题描述

我有一个包含3个表的Postgres数据库，例如 A ， B 和 C 。我想从表 A 中选择数据，并循环遍历每一行，检查其中一列的值，然后将数据插入表 B 或表 C 根据条件。

I have a Postgres database with 3 tables, say A, B and C. I want to select data from table A and loop through each row checking the value in one of the columns and then insert the data into table B or table C based on the condition.

我该怎么做，可以请发布示例脚本。我更喜欢plpgsql（使用PGAdmin3）。

How can I do this, can some one please post a sample script please. I prefer plpgsql (using PGAdmin3).

推荐答案

您不需要游标，不需要plpgsql，您甚至都不需要修改数据的CTE ，这将允许您在单个SQL语句中完成操作。

You don't need a cursor for this, you don't need plpgsql, you don't even need a data-modifying CTE which would allow you to do that in a single SQL statement.

只需运行两个普通 INSERT 语句。如果您要确保应用全部或不应用任何内容，则将它们放入事务中。

Just run two plain INSERT statements. Put them in a transaction if you want to make sure all or nothing is applied:

BEGIN;

INSERT INTO B (col1, col2)
SELECT col1, col2
FROM   A
WHERE  col_cond = 'something';

INSERT INTO C (col1, col2)
SELECT col1, col2
FROM   A
WHERE  col_cond IS DISTINCT FROM 'something';

COMMIT;

这篇关于从一个表中进行选择，然后根据条件插入到另外两个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！