Suppression de doublons impossible ORA-01752

[cmako]

Bonjour,

J'ai un problème, je dois redéfinir ma clé primaire (enlever une colonne), et je dois pour cela supprimer certains doublons.

Le problème est que je n'arrive pas à faire mon DELETE. J'obtiens une erreur ORA-01752, "cannot delete from view without exactly one key-preserved table"

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELETE (
	SELECT t1.* 
	FROM slice t1
	INNER JOIN (
		SELECT SLICE_PC_ID, SLICE_DATE, SLICE_COV_NAME, START_D_VER
		FROM slice
		GROUP BY SLICE_PC_ID, SLICE_DATE, SLICE_COV_NAME, START_D_VER
		HAVING count(1)>1
	) t2
		ON  t1.SLICE_PC_ID = t2.SLICE_PC_ID
		AND t1.SLICE_DATE = t2.SLICE_DATE
		AND t1.SLICE_COV_NAME = t2.SLICE_COV_NAME
		AND t1.START_D_VER = t2.START_D_VER
	WHERE t1.SLICE_STATUS = 'INACTIVE'
)

Je ne sais pas quoi faire, en SQL Server la syntaxe suivante fonctionne et ne crée pas d'erreur

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
DELETE t1
FROM slice t1
INNER JOIN (
	SELECT SLICE_PC_ID, SLICE_DATE, SLICE_COV_NAME, START_D_VER
	FROM slice
	GROUP BY SLICE_PC_ID, SLICE_DATE, SLICE_COV_NAME, START_D_VER
	HAVING count(1)>1
) t2
	ON  t1.SLICE_PC_ID = t2.SLICE_PC_ID
	AND t1.SLICE_DATE = t2.SLICE_DATE
	AND t1.SLICE_COV_NAME = t2.SLICE_COV_NAME
	AND t1.START_D_VER = t2.START_D_VER
WHERE t1.SLICE_STATUS = 'INACTIVE'

Auriez-vous une idée?
Comment supprimer les doublons?

Edit: j'ai même essayé de passer par une table temporaire, mais le con là m'efface la table temporaire et non pas la table "normale"

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
  CREATE TABLE
   temp1
AS 
SELECT t1.*
FROM slice t1
INNER JOIN (
	SELECT SLICE_PC_ID, SLICE_DATE, SLICE_COV_NAME, START_D_VER
	FROM slice
	GROUP BY SLICE_PC_ID, SLICE_DATE, SLICE_COV_NAME, START_D_VER
	HAVING count(1)>1
) t2
	ON 	t1.SLICE_PC_ID = t2.SLICE_PC_ID
	AND t1.SLICE_DATE = t2.SLICE_DATE
	AND t1.SLICE_COV_NAME = t2.SLICE_COV_NAME
	AND t1.START_D_VER = t2.START_D_VER
WHERE t1.SLICE_STATUS = 'INACTIVE';
 
DELETE (
	SELECT t1.* 
	FROM slice t1
	INNER JOIN temp1 t2
		ON 	t1.SLICE_PC_ID = t2.SLICE_PC_ID
		AND t1.SLICE_DATE = t2.SLICE_DATE
		AND t1.SLICE_COV_NAME = t2.SLICE_COV_NAME
		AND t1.START_D_VER = t2.START_D_VER
		AND t1.SLICE_STATUS = t2.SLICE_STATUS
);

[skuatamad]

Utilise rowid:
http://asktom.oracle.com/pls/apex/f?...:1224636375004

[cmako]

Finallement je m'en suis sorti avec ça:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
DELETE 
FROM slice t1
WHERE t1.SLICE_PC_ID = (
	SELECT t2.SLICE_PC_ID
	FROM temp1 t2
	WHERE 	t1.SLICE_PC_ID = t2.SLICE_PC_ID
		AND t1.SLICE_DATE = t2.SLICE_DATE
		AND t1.SLICE_COV_NAME = t2.SLICE_COV_NAME
		AND t1.START_D_VER = t2.START_D_VER
		AND t1.SLICE_STATUS = t2.SLICE_STATUS
)
AND t1.SLICE_DATE = (
	SELECT t2.SLICE_DATE
	FROM temp1 t2
	WHERE 	t1.SLICE_PC_ID = t2.SLICE_PC_ID
		AND t1.SLICE_DATE = t2.SLICE_DATE
		AND t1.SLICE_COV_NAME = t2.SLICE_COV_NAME
		AND t1.START_D_VER = t2.START_D_VER
		AND t1.SLICE_STATUS = t2.SLICE_STATUS
)
AND t1.SLICE_COV_NAME = (
	SELECT t2.SLICE_COV_NAME
	FROM temp1 t2
	WHERE 	t1.SLICE_PC_ID = t2.SLICE_PC_ID
		AND t1.SLICE_DATE = t2.SLICE_DATE
		AND t1.SLICE_COV_NAME = t2.SLICE_COV_NAME
		AND t1.START_D_VER = t2.START_D_VER
		AND t1.SLICE_STATUS = t2.SLICE_STATUS
)
AND t1.START_D_VER = (
	SELECT t2.START_D_VER
	FROM temp1 t2
	WHERE 	t1.SLICE_PC_ID = t2.SLICE_PC_ID
		AND t1.SLICE_DATE = t2.SLICE_DATE
		AND t1.SLICE_COV_NAME = t2.SLICE_COV_NAME
		AND t1.START_D_VER = t2.START_D_VER
		AND t1.SLICE_STATUS = t2.SLICE_STATUS
)
AND t1.SLICE_STATUS = (
	SELECT t2.SLICE_STATUS
	FROM temp1 t2
	WHERE 	t1.SLICE_PC_ID = t2.SLICE_PC_ID
		AND t1.SLICE_DATE = t2.SLICE_DATE
		AND t1.SLICE_COV_NAME = t2.SLICE_COV_NAME
		AND t1.START_D_VER = t2.START_D_VER
		AND t1.SLICE_STATUS = t2.SLICE_STATUS
)

Mais bon, pas très belle la requête.

[skuatamad]

Ben c'est quand même beaucoup plus simple avec rowid, non ?