同一表中与 ID 匹配的列的 UPDATE NULL
我正在处理多列的住房数据。在清理过程中,我注意到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.00 | NULL | 092 13 0 322.00 | 237 37TH AVE N,纳什维尔 |
043 04 0 014.00 | NULL | 043 04 0 014.00 | 112 HILLER DR,老山核桃 |
026 05 0 017.00 | NULL | 026 05 0 017.00 | 208 EAST AVE, GOODLETTSVILLE |
042 13 0 075.00 | NULL | 042 13 0 075.00 | 222 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;
ParcelID | PropertyAddress | ParcelID | PropertyAddress |
---|---|---|---|
092 13 0 322.00 | NULL | 092 13 0 322.00 | 237 37TH AVE N, NASHVILLE |
043 04 0 014.00 | NULL | 043 04 0 014.00 | 112 HILLER DR, OLD HICKORY |
026 05 0 017.00 | NULL | 026 05 0 017.00 | 208 EAST AVE, GOODLETTSVILLE |
042 13 0 075.00 | NULL | 042 13 0 075.00 | 222 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 技术交流群。

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