基于两个表之间的子查询的Oracle SQL更新 [英] Oracle SQL update based on subquery between two tables

问题描述

我目前正在编写更新语句，以使可查询表保持最新状态.这两个表之间的架构相同，内容也不重要:

I am currently writing update statements to keep a query-able table constantly up to date. The schema is identical between both tables and the contents are not important:

STAGING  

ID  
NAME  
COUNT    

PRODUCTION  

ID  
NAME  
COUNT

我的更新语句如下:

update PRODUCTION  
set name = (select stage.name from staging stage where stage.name=name  and rownum <2),  
    count =   (select stage.countfrom staging stage where stage.count=count  and rownum <2);

需要注意的两件事是:1)更新结束时没有where子句(这可能是问题所在)； 2)更新后的所有记录都具有相同的值.我的意思是:

The two things of note is that 1) There is no where clause at the end of my update (this may be the problem) and 2) all records after being updated have the same values. What I mean by this is the following:

BEFORE UPDATE:  

1,"JOHN", 12;  
2,"STEVE",15;  
3,"BETTY",2;  

AFTER UPDATE  

    1,"JOHN", 12;  
    2,"JOHN",12;  
    3,"JOHN",12;

我的问题是如何解决此问题，以使表正确地将登台的新"数据反映为正确的SQL更新?

My question is how do I fix this so that the table properly reflects "new" data from staging as a correct SQL update?

更新

因此，我的登台数据可以恰好反映PRODUCTION中的内容，为了便于讨论，它将:

So my staging data could coincidentally mirror what is in PRODUCTION and for the sake of discussion it will:

STAGING DATA TO MERGE:  

    1,"JOHN", 12;  
    2,"STEVE",15;  
    3,"BETTY",2; 

更新第二个

我想运行的查询是这样:

The query that I would like to run would be this:

update PRODUCTION
set production.name = staging.name,  
    production.count = staging.count

where production.name = staging.name;

但这会导致在"staging.name"上出现无效的标识符问题

This however results in invalid identifier issues on "staging.name"

推荐答案

有两种方法可以完成您要尝试的操作

There are two ways to do what you are trying

一个是多列相关更新

UPDATE PRODUCTION a
SET (name, count) = (
  SELECT name, count
  FROM STAGING b
  WHERE a.ID = b.ID);

演示

您可以使用合并

MERGE INTO PRODUCTION a
USING ( select id, name, count 
          from STAGING ) b
ON ( a.id = b.id )
WHEN MATCHED THEN 
UPDATE SET  a.name = b.name,
            a.count = b.count

演示

这篇关于基于两个表之间的子查询的Oracle SQL更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！