ORACLE DB-In a situation that few dates data loaded as null for one column&remaining dates with correct data. null should be replace with correct one

huangapple go评论68阅读模式
英文:

ORACLE DB-In a situation that few dates data loaded as null for one column&remaining dates with correct data. null should be replace with correct one

问题

以下是情景描述,
有一张名为'test'的表,其中的数据如下:

column1 column2 column3
A        null     7/14/23
A        null     7/15/23
A        B        7/16/23
X        null     7/14/23
X        null     7/15/23
X        G       7/16/23

这是表'test'中加载的数据。

现在我需要更新记录,如下(将null替换为最近加载的数据):

column1 column2  column3
A        B        7/14/23
A        B        7/15/23
A        B        7/16/23
X        G        7/14/23
X        G        7/15/23
X        G        7/16/23

请帮助我如何更新表记录。
注意- 这张表没有任何主键。

我期望Oracle PL/SQL专家能够给我一个思路,如何更新记录而不影响正确加载的其他日期。

英文:

Below is the scenario,
There is a table called 'test' have data like this

column1 column2 column3  
A        null     7/14/23  
A        null     7/15/23  
A        B        7/16/23  
X        null     7/14/23  
X        null     7/15/23  
X        G       7/16/23   

This how data loaded in table 'test'

Now i have to update the records like below (null should be replace with the data which recently loaded)

column1 column2  column3  
A        B        7/14/23  
A        B        7/15/23  
A        B        7/16/23  
X        G        7/14/23  
X        G        7/15/23  
X        G        7/16/23     

Please help me how can i update the table records
Note- this table doesn't have any primary key

Im expecting oracle plsql techies to give me idea how can i update records without affecting other dates which loaded correctly

答案1

得分: 0

你可以使用以下代码段作为MERGE语句中正确数据的来源:

select column1, first_value(column2) over(partition by column1 order by column3 desc) as column2, column3 
from data

(前提是你能唯一标识每一行...)

英文:

You can use

select column1, first_value(column2) over(partition by column1 order by column3 desc) as column2, column3 
from data 

as source of the correct data in a MERGE statement.
(at condition you can identify uniquely each row...)

huangapple
  • 本文由 发表于 2023年7月20日 22:20:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/76730852.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定