By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE POI
("poi_id" int GENERATED BY DEFAULT AS IDENTITY,
"city_id" int,
PRIMARY KEY("poi_id")
);
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
;
5 rows affected
CREATE TABLE newCities
("city_id" int, "new_city_id" int)
;
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
;
3 rows affected
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 |
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";
ORA-01779: cannot modify a column which maps to a non key-preserved table
UPDATE poi p
SET "city_id" = COALESCE((SELECT "new_city_id"
FROM newCities c
WHERE c."city_id" = p."city_id")
, p."city_id");
5 rows affected
SELECT * FROM poi;
| poi_id | city_id |
|---|---|
| 10 | 100 |
| 11 | 100 |
| 12 | 200 |
| 13 | 200 |
| 14 | 5 |