Discussion :

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?