我也不明白ORA-01779是指哪一栏。
我也不明白ORA-01779是指哪一栏。
提问于 2019-09-24 16:12:25
我有一张带有POI的桌子，你可以在每个城市有多个POI。
CREATE TABLE POI
("poi_id" int GENERATED BY DEFAULT AS IDENTITY,
"city_id" int,
PRIMARY KEY("poi_id")
);现在，城市多边形发生了一些变化，现在不得不重新分配一些POI。
CREATE TABLE newCities
("city_id" int, "new_city_id" int)
;数据
INSERT ALL
INTO POI ("poi_id", "city_id")
VALUES (10, 1)
INTO POI ("poi_id", "city_id")
VALUES (11, 1)
INTO POI ("poi_id", "city_id")
VALUES (12, 2)
INTO POI ("poi_id", "city_id")
VALUES (13, 2)
INTO POI ("poi_id", "city_id")
VALUES (14, 5)
SELECT * FROM dual
;
INSERT ALL
INTO newCities ("city_id", "new_city_id")
VALUES (1, 100)
INTO newCities ("city_id", "new_city_id")
VALUES (2, 200)
INTO newCities ("city_id", "new_city_id")
VALUES (3, 200)
SELECT * FROM dual
;当我加入时：
SELECT *
FROM poi p
JOIN newCities nc
ON p."city_id" = nc."city_id";输出
+--------+---------+---------+-------------+
| poi_id | city_id | city_id | new_city_id |
+--------+---------+---------+-------------+
| 10 | 1 | 1 | 100 |
| 11 | 1 | 1 | 100 |
| 12 | 2 | 2 | 200 |
| 13 | 2 | 2 | 200 |
+--------+---------+---------+-------------+但是，当我尝试进行更新但得到错误时：
ORA-01779:无法修改映射到非密钥保存表的列。
UPDATE (
SELECT p.*, nc."new_city_id"
FROM poi p
JOIN newCities nc
ON p."city_id" = nc."city_id"
) t
SET t."city_id" = t."new_city_id";我知道city_id不是PK，但该行与一行(包括PK )匹配。那为什么不起作用？
我知道我可以做一个子查询来获得值：
UPDATE poi p
SET "city_id" = COALESCE((SELECT "new_city_id"
FROM newCities c
WHERE c."city_id" = p."city_id")
, p."city_id");但是仍然想知道更新连接会在什么情况下起作用，因为看起来只有在更新PK时才能工作：
回答 1
Stack Overflow用户
发布于 2019-09-24 16:20:00
完成示例测试后，决定尝试在第二个表上创建一个PK，然后工作：
CREATE TABLE newCities
("city_id" int GENERATED BY DEFAULT AS IDENTITY,
"new_city_id" int,
PRIMARY KEY("city_id")
);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58084379
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号