插入临时表并在一个SQL查询(Oracle)中更新另一个表 [英] Insert into a temporary table and update another table in one SQL query (Oracle)

问题描述

这就是我想要做的:

1)将临时表中的某些值插入到临时表中

1) Insert into a temp table some values from an original table

INSERT INTO temp_table SELECT id FROM original WHERE status='t'

2)更新原始表

UPDATE original SET valid='t' WHERE status='t'

3)基于两个表之间的联接进行选择

3) Select based on a join between the two tables

SELECT * FROM original WHERE temp_table.id = original.id

是否可以将步骤1和步骤2组合在一起?

Is there a way to combine steps 1 and 2?

推荐答案

您可以通过在PL/SQL中进行更新并使用RETURNING子句将更新后的ID放入PL/SQL表中来组合这些步骤.

You can combine the steps by doing the update in PL/SQL and using the RETURNING clause to get the updated ids into a PL/SQL table.

如果仍然需要执行最终查询，则仍然可以使用此方法将其插入temp_table中.尽管根据最后一个查询的目的，可能还有其他方式可以实现您想要的.为了说明:

If you still need to do the final query, you can still use this method to insert into the temp_table; although depending on what that last query is for, there may be other ways of achieving what you want. To illustrate:

DECLARE
  id_table_t IS TABLE OF original.id%TYPE INDEX BY PLS_INTEGER;
  id_table id_table_t;
BEGIN
  UPDATE original SET valid='t' WHERE status='t'
  RETURNING id INTO id_table;
  FORALL i IN 1..id_table.COUNT
    INSERT INTO temp_table
    VALUES (id_table(i));
END;
/

SELECT * FROM original WHERE temp_table.id = original.id;

这篇关于插入临时表并在一个SQL查询(Oracle)中更新另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！