同一表中与 ID 匹配的列的 UPDATE NULL

发布于 01-14 01:36 字数 1967 浏览 6 评论 0原文

我正在处理多列的住房数据。在清理过程中,我注意到PropertyAddress一栏中有几个Null,我想确认这些Null是否可以与ParcelID匹配。因此,我编写了以下查询来确认之前的确认,结果显示在查询下方。

SELECT a.ParcelID, a.PropertyAddress, b .ParcelID, b.PropertyAddress
FROM nashville_housing a
JOIN nashville_housing b
    on a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress is null;
地块 ID属性地址地块 ID属性地址
092 13 0 322.00NULL092 13 0 322.00237 37TH AVE N,纳什维尔
043 04 0 014.00NULL043 04 0 014.00112 HILLER DR,老山核桃
026 05 0 017.00NULL026 05 0 017.00208 EAST AVE, GOODLETTSVILLE
042 13 0 075.00NULL042 13 0 075.00222 FOXBORO DR, MADISON

在确认我可以使用 ParcelID 使用正确的 PropertyAddress 更改 Null 后,我编写了 UPDATE 查询:

UPDATE nashville_housing
SET PropertyAddress = ( 
SELECT a.ParcelID, b.PropertyAddress, b .ParcelID, b.PropertyAddress
FROM nashville_housing a
JOIN nashville_housing b
    on a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress is null);

并给出错误“错误代码:1241。操作数应包含 1 列”

因此,我将查询重写为:

UPDATE a
SET PropertyAddress = IFNULL(a.PropertyAddress,b.PropertyAddress)
WHERE a.PropertyAddress is null;

并给出错误“错误代码:1146。表“nasville_housing.a”不包含'不存在'

最后,我写道:

UPDATE a
SET PropertyAddress = IFNULL(a.PropertyAddress,b.PropertyAddress) in (
SELECT a.ParcelID, b.PropertyAddress, b .ParcelID, b.PropertyAddress
FROM nashville_housing a
JOIN nashville_housing b
    on a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress is null);

但给出错误'错误代码:1146。表'nasville_housing.a'不存在'

我感谢任何人都可以提供的支持 我。

I'm working on housing data with several columns. In the cleaning process, I noticed that in the column 'PropertyAddress' are a few Null, and I want to confirm if those Null could be matched with the ParcelID. So, I wrote the following query to confirm the previous affirmation with the result shown below the query.

SELECT a.ParcelID, a.PropertyAddress, b .ParcelID, b.PropertyAddress
FROM nashville_housing a
JOIN nashville_housing b
    on a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress is null;
ParcelIDPropertyAddressParcelIDPropertyAddress
092 13 0 322.00NULL092 13 0 322.00237 37TH AVE N, NASHVILLE
043 04 0 014.00NULL043 04 0 014.00112 HILLER DR, OLD HICKORY
026 05 0 017.00NULL026 05 0 017.00208 EAST AVE, GOODLETTSVILLE
042 13 0 075.00NULL042 13 0 075.00222 FOXBORO DR, MADISON

After confirming that I could use ParcelID to change the Nulls with the correct PropertyAddress, I wrote the UPDATE query:

UPDATE nashville_housing
SET PropertyAddress = ( 
SELECT a.ParcelID, b.PropertyAddress, b .ParcelID, b.PropertyAddress
FROM nashville_housing a
JOIN nashville_housing b
    on a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress is null);

and give the error 'Error Code: 1241. Operand should contain 1 column(s)'

So, I rewrite the query to:

UPDATE a
SET PropertyAddress = IFNULL(a.PropertyAddress,b.PropertyAddress)
WHERE a.PropertyAddress is null;

and give the error 'Error Code: 1146. Table 'nasville_housing.a' doesn't exist'

Finally, I wrote:

UPDATE a
SET PropertyAddress = IFNULL(a.PropertyAddress,b.PropertyAddress) in (
SELECT a.ParcelID, b.PropertyAddress, b .ParcelID, b.PropertyAddress
FROM nashville_housing a
JOIN nashville_housing b
    on a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress is null);

but give the error 'Error Code: 1146. Table 'nasville_housing.a' doesn't exist'

I appreciate the support anyone can give me.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

淡看悲欢离合2025-01-21 01:36:41

您可以使用 JOIN 进行更新: https://www.mysqltutorial.org/mysql-update-join /

UPDATE nashville_housing a
LEFT JOIN nashville_housing b ON a.ParcelID = b.ParcelID 
AND a.UniqueID <> b.UniqueID 
SET a.PropertyAddress = b.PropertyAddress 
WHERE
    a.PropertyAddress IS NULL;

You can UPDATE using JOIN: https://www.mysqltutorial.org/mysql-update-join/

UPDATE nashville_housing a
LEFT JOIN nashville_housing b ON a.ParcelID = b.ParcelID 
AND a.UniqueID <> b.UniqueID 
SET a.PropertyAddress = b.PropertyAddress 
WHERE
    a.PropertyAddress IS NULL;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文