我如何更新一个列与空值存在一个类似的主键在Oracle SQL?

Question

例如，对于纳什维尔住房数据集，我试图填写空条目的属性地址，但对于唯一id应该是相同的。我试图创建两个表，然后更新一个表列中的空条目，并尝试将它们填充到下一个。

我有微软sql server的代码，但我有麻烦转换到Oracle。

Update a
SET PropertyAddress = ISNULL(a.PropertyAddress,b.PropertyAddress)
From PortfolioProject.dbo.NashvilleHousing a
JOIN PortfolioProject.dbo.NashvilleHousing b
on a.ParcelID = b.ParcelID
AND a.[UniqueID ] <> b.[UniqueID ]
Where a.PropertyAddress is null

这是纳什维尔房屋日期集的链接:

https://github.com/AlexTheAnalyst/PortfolioProjects/blob/main/Nashville%20Housing%20Data%20for%20Data%20Cleaning.xlsx

Answer 1

您似乎可以使用MERGE语句:

MERGE INTO NashvilleHousing dst
USING NashvilleHousing src
ON (
dst.parcelid = src.parcelid
AND dst.uniqueid <> src.uniqueid
)
WHEN MATCHED THEN
UPDATE
SET    propertyaddress = src.propertyaddress
WHERE  dst.propertyaddress IS NULL;

或UPDATE语句中的相关子查询:

UPDATE NashvilleHousing dst
SET propertyaddress = (
SELECT src.propertyaddress
FROM   NashvilleHousing src
WHERE  dst.parcelid = src.parcelid
AND    dst.uniqueid <> src.uniqueid
AND    src.propertyaddress IS NOT NULL
AND    ROWNUM = 1
)
WHERE propertyaddress IS NULL

db<此处小提琴>