如何更新现有记录
如何更新现有记录
提问于 2018-06-10 05:50:27
我有一个具有层次结构的location表：
国家/地区>国家/地区>城市>站点
位置名称唯一。LOCATION2PARENTLOCATION列具有层次结构关系。
CREATE TABLE "LOCATION"
("LOCATIONID" NUMBER,
"NAME" VARCHAR2(50 CHAR),
"ALIAS1" VARCHAR2(50 CHAR),
"TOWNCITY" VARCHAR2(50 CHAR),
"STATE" VARCHAR2(50 CHAR),
"COUNTRY" VARCHAR2(50 CHAR),
"LOCATION2PARENTLOCATION" NUMBER,
"LOCATION2LOCATIONTYPE" VARCHAR2(50 CHAR),
CONSTRAINT location_id_pk PRIMARY KEY (LOCATIONID),
CONSTRAINT location_name_uq UNIQUE(NAME)
);
INSERT INTO LOCATION VALUES (1,'India',null,null,null,null,null,'COUNTRY');
INSERT INTO LOCATION VALUES (2,'Bihar','BH',null,null,null,1,'STATE');
INSERT INTO LOCATION VALUES (3,'Maharashtra','MH',null,null,null,1,'STATE');
INSERT INTO LOCATION VALUES (4,'Aurangabad',null,null,null,null,2,'CITY');
INSERT INTO LOCATION VALUES (5,'Patna',null,null,null,null,2,'CITY');
INSERT INTO LOCATION VALUES (6,'Pune',null,null,null,null,3,'CITY');
INSERT INTO LOCATION VALUES (8,'Aurangabad,BH',null,null,null,null,2,'CITY');
INSERT INTO LOCATION VALUES (9,'Aurangabad,MH',null,null,null,null,3,'CITY');
INSERT INTO LOCATION VALUES (10,'Deo',null,'Aurangabad','Bihar','India',4,'SITE');
INSERT INTO LOCATION VALUES (11,'Obra',null,'Aurangabad','Bihar','India',4,'SITE');
INSERT INTO LOCATION VALUES (12,'Kutumba',null,'Aurangabad,BH','Bihar','India',8,'SITE');
INSERT INTO LOCATION VALUES (13,'Dura',null,'Aurangabad,BH','Bihar','India',8,'SITE');
INSERT INTO LOCATION VALUES (14,'Dhanpura',null,'Patna','Bihar','India',5,'SITE');
INSERT INTO LOCATION VALUES (15,'Shekhpura',null,'Patna','Bihar','India',5,'SITE');
INSERT INTO LOCATION VALUES (16,'Hadapsar',null,'Pune','Maharashtra','India',6,'SITE');
INSERT INTO LOCATION VALUES (17,'Baner',null,'Pune','Maharashtra','India',6,'SITE');
INSERT INTO LOCATION VALUES (18,'Cidco',null,'Aurangabad,MH','Maharashtra','India',9,'SITE');
INSERT INTO LOCATION VALUES (19,'Mukundwadi',null,'Aurangabad,MH','Maharashtra','India',9,'SITE');问题陈述：
有一些城市名称在多个州之间是通用的。
例如。奥兰加巴德是马哈拉施特拉邦和比哈尔邦的一个城市，就像许多其他例子一样。因为名称是唯一的，所以表不允许我在具有层次结构的位置表中插入重复的城市名称。
问题的
解决方案：
为了维护位置之间的层次结构，我决定在城市名称中附加州代码(州的Alias1列)。这将是一个唯一的名称。
为。例如：
来自比哈尔的
- 将用'Aurangabad，BH'
- Aurangabad‘来自马哈拉施特拉邦的
- 将用'Aurangabad，MH'
更新
SQL问题：
我想更新所有城市的名称与‘城市，StateCode’在旧的现有记录。但是有一些城市已经创建了'Aurangabad‘和'Aurangabad，BH’都在同一状态下。它们不允许我更新旧的现有记录。
UPDATE LOCATION L1
SET L1.NAME= L1.NAME ||','||(SELECT L2.ALIAS1 FROM LOCATION L2 WHERE L1.location2parentlocation = L2.LOCATIONID AND L2.location2LOCATIONTYPE='STATE')
WHERE L1.location2locationtype='CITY'
and L1.name not like '%,%';
UPDATE LOCATION L10
SET TOWNCITY= (SELECT NAME FROM LOCATION L11 WHERE L11.LOCATIONID=L10.location2parentlocation AND L11.location2LOCATIONTYPE='CITY')
WHERE L10.LOCATION2LOCATIONTYPE='SITE';
Oracle Error:
SQL Error: ORA-00001: unique constraint (LOCATION_NAME_UQ) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.如果我用'City，StateCode‘删除记录，上面的update语句将起作用，但我不希望删除任何记录。
delete from location where locationid in (8,9,12,13,18,19);问题：
如何更新旧的现有记录并维护层次结构关系？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-10 08:12:53
，但我不希望删除任何记录。
在我看来，你确实有重复的数据。LOCATIONID 4和LOCATIONID 8真正代表了同一个城市，其中一个应该去。
如果这是正确的，首先需要将城市4和8的站点记录合并到一个ID下，然后我们可以删除另一个ID。
下面是一个(带注释的) MERGE语句，它可以完成所有这些任务。
它基本上计算出我们想要如何重命名和重新编号城市，识别具有给定名称的最低城市ID &州作为我们将保留的ID。它将合并将保留的城市下的站点记录，并删除重复和不再需要的城市。
MERGE INTO location t
USING (
-- This query will list each location along with (for cities and sites) the new city name and city ID to use
WITH city_rename AS (
SELECT city.locationid,
city.name city_name,
state.name state_name,
state.alias1 state_alias1,
case when city.name like '%,' || state.alias1 THEN city.name ELSE city.name || ',' || state.alias1 END new_city_name,
row_number() over ( partition by case when city.name like '%,' || state.alias1 THEN city.name ELSE city.name || ',' || state.alias1 END order by city.locationid ) rn,
min(city.locationid) over ( partition by case when city.name like '%,' || state.alias1 THEN city.name ELSE city.name || ',' || state.alias1 END order by city.locationid ) new_city_id
FROM location city
INNER JOIN location state ON state.locationid = city.location2parentlocation
WHERE city.location2locationtype = 'CITY' )
SELECT l.*, cr.new_city_name, cr.new_city_id FROM location l
LEFT JOIN city_rename cr ON cr.locationid IN (l.locationid, l.location2parentlocation) ) u
ON ( t.locationid = u.locationid )
WHEN MATCHED THEN
-- Update each CITY and SITE record according to the city renaming results above
UPDATE SET
-- Rename the city
t.name = CASE WHEN t.location2locationtype = 'CITY' THEN u.new_city_name ELSE t.name END,
-- Redirect the sites to the unique city ID for the new city name
t.location2parentlocation = CASE WHEN t.location2locationtype = 'SITE' THEN u.new_city_id ELSE t.location2parentlocation END,
-- Rename the towncity for the sites
t.towncity = CASE WHEN t.location2locationtype = 'SITE' THEN u.new_city_name ELSE t.towncity END
-- Finally, as part of the MERGE, delete any cities that are not unique for the new city name. Any SITE records
-- previously tied to this city will have been redirect to the main ID for the new city name by the above logic
DELETE WHERE t.location2locationtype = 'CITY' and u.new_city_id != t.locationid;在上面的MERGE之后，您的LOCATION表如下所示：
+------------+---------------+--------+---------------+-------------+---------+-------------------------+-----------------------+
| LOCATIONID | NAME | ALIAS1 | TOWNCITY | STATE | COUNTRY | LOCATION2PARENTLOCATION | LOCATION2LOCATIONTYPE |
+------------+---------------+--------+---------------+-------------+---------+-------------------------+-----------------------+
| 1 | India | - | - | - | - | - | COUNTRY |
| 2 | Bihar | BH | - | - | - | 1 | STATE |
| 3 | Maharashtra | MH | - | - | - | 1 | STATE |
| 4 | Aurangabad,BH | - | - | - | - | 2 | CITY |
| 5 | Patna,BH | - | - | - | - | 2 | CITY |
| 6 | Pune,MH | - | - | - | - | 3 | CITY |
| 9 | Aurangabad,MH | - | - | - | - | 3 | CITY |
| 10 | Deo | - | Aurangabad,BH | Bihar | India | 4 | SITE |
| 11 | Obra | - | Aurangabad,BH | Bihar | India | 4 | SITE |
| 12 | Kutumba | - | Aurangabad,BH | Bihar | India | 4 | SITE |
| 13 | Dura | - | Aurangabad,BH | Bihar | India | 4 | SITE |
| 14 | Dhanpura | - | Patna,BH | Bihar | India | 5 | SITE |
| 15 | Shekhpura | - | Patna,BH | Bihar | India | 5 | SITE |
| 16 | Hadapsar | - | Pune,MH | Maharashtra | India | 6 | SITE |
| 17 | Baner | - | Pune,MH | Maharashtra | India | 6 | SITE |
| 18 | Cidco | - | Aurangabad,MH | Maharashtra | India | 9 | SITE |
| 19 | Mukundwadi | - | Aurangabad,MH | Maharashtra | India | 9 | SITE |
+------------+---------------+--------+---------------+-------------+---------+-------------------------+-----------------------+注意LOCATIONID 8是如何消失的，以前引用它的所有记录现在都引用了LOCATIONID 4。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50778833
复制相关文章
相似问题