我有一个具有层次结构的位置表:
国家/地区>州>城市>网站
位置名称是唯一的。
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');
问题陈述:
有些城市名称在多个州中很常见。
例如,奥朗加巴德是马哈拉施特拉邦和比哈尔邦的一个城市,就像其他许多例子一样。
因为名称是唯一的,所以表不允许我在具有层次结构的位置表中插入重复的城市名称。
问题解决方法:
为了维护位置之间的层次结构,我决定在城市名称中附加状态代码(状态的别名1列)。
这将是一个唯一的名称。
为了。如。
-
Patna City将更新为“Patna,bh”
-
双关语和“双关语,mh”
-
比哈尔邦的奥朗加巴德将更新为“奥朗加巴德,BH”
-
马哈拉施特拉邦的奥朗加巴德将更新为“奥朗加巴德,mh”。
SQL问题:
我想在旧的现有记录中用'city,statecode'更新所有城市名称。
但有些城市已经建立了“奥朗加巴德”和“奥朗加巴德,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”的记录,上面的更新语句将起作用,但我不希望删除任何记录。
delete from location where locationid in (8,9,12,13,18,19);
Question:
如何更新旧的现有记录并维护层次关系?
