1
votes

Comment mettre à jour en rejoignant la table

J'ai trois tables.

Stude_course et ses enregistrements comme ci-dessous

empid   ename   emp_status  emp_year
1   Raja        6           1
2   Poo         6           1
3   Bhasker     6           1

But i got error like 

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

La table Student1 contient les enregistrements ci-dessous

update
(
select sc.emp_status stud_emp_status,sc.emp_year stud_emp_year,s.emp_status stud_status,sy.emp_year stud_year from stude_course sc,student1 s,stud_year sy
where sc.empid = s.empid
and s.empid = sy.empid
and sc.empid = 2) st
set st.stud_emp_status = st.stud_status, st.stud_emp_year = st.stud_year;

Stud_year contient les enregistrements ci-dessous

empid   emp_year
1       1
2       1
3       1

J'ai besoin d'une requête pour mettre à jour stude_course.emp_status en utilisant student1.emp_status et pour mettre à jour stude_course.emp_year en utilisant stud_year.emp_year. Afin de mettre à jour ces enregistrements, j'ai utilisé la requête ci-dessous

empid   ename   emp_status
1   Raja        6
2   Poo         6
3   Bhasker     6

J'ai joint trois tables en utilisant equi join et j'ai donné un nom d'alias en tant que st pour les tables jointes globales et également un nom d'alias pour le nom de la colonne, puis j'ai essayé de mettre à jour les valeurs avec l'utilisation de la table d'alias et du nom de colonne donnés

Résultat attendu:

empid   ename   emp_status  emp_year
1   Raja    6           1
2   Poo     5           2
3   Bhasker 6           3


0 commentaires

3 Réponses :


1
votes

Vous pouvez directement mettre à jour le tableau comme suit:

MERGE INTO STUDE_COURSE SC
USING
(SELECT SY.EMP_YEAR, S1.EMP_STATUS, S1.EMPID FROM STUD_YEAR SY JOIN STUDENT1 S1 
ON (S1.EMPID = SY.EMPID)) S
ON (SC.EMPID = S.EMPID)
WHEN MATCHED THEN
UPDATE SET SC.EMP_STATUS = S.EMP_STATUS, SC.EMP_YEAR = S.EMP_YEAR

ou vous pouvez également utiliser MERGE comme suit:

UPDATE STUDE_COURSE SC
SET (SC.EMP_YEAR, SC.EMP_STATUS) = 
(SELECT SY.EMP_YEAR, S1.STATUS FROM STUD_YEAR SY JOIN STUDENT1 S1 
ON (S1.EMPID = SY.EMPID)
WHERE SY.EMPID = SC.EMPID)
WHERE SC.EMPID = 2;


0 commentaires

1
votes

Je ne suis pas sûr que la syntaxe de la sous-requête de mise à jour que vous utilisez soit réalisable lorsqu'une jointure est impliquée. Nous pouvons essayer d'écrire la mise à jour en utilisant des sous-requêtes corrélées à la place:

UPDATE stude_course sc
SET
    emp_status = (SELECT s.emp_status
                  FROM student1 s
                  INNER JOIN stud_year sy ON s.empid = sy.empid
                  WHERE sc.empid = s.empid),
    emp_year = (SELECT sy.emp_year
                FROM student1 s
                INNER JOIN stud_year sy ON s.empid = sy.empid
                WHERE sc.empid = s.empid)
WHERE
    sc.empid = 2;


2 commentaires

Cela peut également être écrit avec une seule sous-requête. (emp_status, emp_year) = (sélectionnez s.emp_status, sy.emp_yer de ...)


@a_horse_with_no_name Excellent truc ... J'essaierai de m'en souvenir!



2
votes

ne peut pas modifier une colonne qui correspond à une table non conservée par clé

Les colonnes que vous essayez de mettre à jour se trouvent dans la table STUDE_COURSE . Mais Oracle, après avoir examiné les structures de vos tables, a décidé que votre requête de jointure n'est pas garantie d'inclure chaque ligne dans STUDE_COURSE une seule fois.

Si vous ajoutez des contraintes à vos tables, garantira à Oracle que les lignes de STUDE_COURSE ne seront pas dupliquées dans votre requête, alors votre UPDATE fonctionnera.

Passons en revue.

Tout d'abord, recréons votre situation actuelle:

Configurer les tableaux et données

update
(
select sc.emp_status stud_emp_status,
       sc.emp_year stud_emp_year,
       s.emp_status stud_status,
       sy.emp_year stud_year 
from stude_course sc INNER JOIN student1 s ON s.empid=sc.empid
INNER JOIN stud_year sy ON sy.empid=s.empid
where sc.empid = 2) st
set st.stud_emp_status = st.stud_status, st.stud_emp_year = st.stud_year;

Tentative de UPDATE (échoue)

alter table stude_course add constraint stud_course_pk PRIMARY KEY ( empid );

alter table student1 add constraint student1_pk PRIMARY KEY ( empid );

alter table stud_year add constraint stud_year_pk PRIMARY KEY ( empid );
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

Ajoutez des contraintes pour indiquer à Oracle que les jointures ne dupliqueront pas les lignes de la table cible

update
(
select sc.emp_status stud_emp_status,
       sc.emp_year stud_emp_year,
       s.emp_status stud_status,
       sy.emp_year stud_year 
from stude_course sc INNER JOIN student1 s ON s.empid=sc.empid
INNER JOIN stud_year sy ON sy.empid=s.empid
where sc.empid = 2) st
set st.stud_emp_status = st.stud_status, st.stud_emp_year = st.stud_year;

Réessayez ... (fonctionne)

XXX

1 ligne mise à jour.

Avertissement concernant les performances

Ce type de syntaxe n'est pas courant (voir d'autres réponses publiées pour des alternatives) et j'ai rencontré une situation étrange avec celle-ci une fois. Ce qui s'est passé, c'est que le CBO a optimisé la jointure de telle sorte que l'ordre des lignes dans le jeu de résultats n'était pas le même que l'ordre des lignes de la table en cours de mise à jour. En conséquence, Oracle a mis à jour les blocs partout, touchant chaque bloc plusieurs fois, ce qui a entraîné de très mauvaises performances. L'ajout d'un ORDER BY target_table.rowid l'a corrigé. C'était un vrai casse-tête.


0 commentaires