如何更新现有记录
如何更新现有记录
提问于 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);问题：
如何更新旧的现有记录并维护层次结构关系？
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50778833
复制相关文章
相似问题