英文:
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...)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论