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评论100阅读模式
英文:

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'的表,其中的数据如下:

  1. column1 column2 column3
  2. A null 7/14/23
  3. A null 7/15/23
  4. A B 7/16/23
  5. X null 7/14/23
  6. X null 7/15/23
  7. X G 7/16/23

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

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

  1. column1 column2 column3
  2. A B 7/14/23
  3. A B 7/15/23
  4. A B 7/16/23
  5. X G 7/14/23
  6. X G 7/15/23
  7. X G 7/16/23

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

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

英文:

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

  1. column1 column2 column3
  2. A null 7/14/23
  3. A null 7/15/23
  4. A B 7/16/23
  5. X null 7/14/23
  6. X null 7/15/23
  7. 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)

  1. column1 column2 column3
  2. A B 7/14/23
  3. A B 7/15/23
  4. A B 7/16/23
  5. X G 7/14/23
  6. X G 7/15/23
  7. 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语句中正确数据的来源:

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

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

英文:

You can use

  1. select column1, first_value(column2) over(partition by column1 order by column3 desc) as column2, column3
  2. 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:

确定