J'essaie d'insérer des lignes dans une table vide qui stockent les informations client d'une autre table. La clé primaire est ID et j'utilise la requête suivante pour sélectionner les enregistrements:
INSERT INTO client (id, name, surname, surname2, dob, phone, email, address) SELECT DISTINCT NVL(cl_dni, floor(dbms_random.value(10000000,100000000))), cl_name, cl_surn1, cl_surn2, cl_birth, cl_phone, cl_email, cl_address FROM purchases WHERE NOT EXISTS(SELECT id from client WHERE client.id = purchases.cl_dni);
Mon objectif principal est de générer un entier aléatoire pour id s'il est nul et de m'assurer que je n'ajoute pas dupliquer les valeurs d'identifiant dans la table, mais cela me donne une erreur de violation de contrainte unique. Cela signifie-t-il qu'il y a un problème avec la clause WHERE NOT EXISTS? Comment utiliser INSERT INTO SELECT pour éviter les valeurs de clé primaire en double?
3 Réponses :
Quelle est la contrainte unique sur la table client? Puis-je supposer qu'il y a des dups dans la table des achats car vous utilisez distinct lors de la récupération des données? Il peut y avoir une possibilité qu'il y ait plusieurs enregistrements dans les achats, un avec un ID client valide et un avec un ID client nul, mais les autres valeurs d'attribut sont les mêmes. De cette façon, vous essayez d'insérer 2 enregistrements dans la table client à partir d'achats, l'un avec un identifiant valide et l'autre avec une valeur aléatoire, mais les mêmes attributs pour la contrainte unique définie sur la table client.
La seule contrainte unique sur la table client est la clé primaire. Il y a des doublons dans la table des achats car certains clients ont fait plusieurs achats, donc les colonnes que j'essaie d'insérer dans la nouvelle table (informations client) sont les mêmes pour ces lignes.
Eh bien, aléatoire ne garantit pas l'unicité, et la partie NOT Exists ne trouvera rien car la table cible est vide.
Ce n'est pas si facile en SQL pur mais comme vous utilisez une base de données Oracle, vous pouvez facilement le faire en plsql. Cela devrait fonctionner en principe :). Non testé.
INSERT INTO client (id, name, surname, surname2, dob, phone, email, address)
SELECT DISTINCT NVL(cl_dni, mv.val + rownum), cl_name, cl_surn1, cl_surn2, cl_birth, cl_phone, cl_email, cl_address
FROM purchases,
(select max(cl_dni) val from purchases) mv;
MODIFIER Une autre approche serait de trouver la valeur maximale que j'achète et d'ajouter le rownum pour obtenir un identifiant unique. Essayez ceci, non testé ...
declare
iOffset pls_interger;
begin
-- Use max value as offset
select max(cl_dni) into iOffset from purchases;
-- Use 1 if no id exists at all
iOffset := nvl(iOffset,1);
-- Loop thru all purchases
for rec in (SELECT distinct cl_dni,cl_name, cl_surn1, cl_surn2, cl_birth, cl_phone, cl_email, cl_address FROM purchases) loop
INSERT INTO client (id, name, surname, surname2, dob, phone, email, address)
VALUES (NVL(rec.id, iOffset), rec.cl_name ..... );
iOffset := iOffset + 1;
end loop;
end;
Mais lorsque je remplis le tableau, il N'EXISTE PAS comparer les valeurs avec les valeurs que je viens d'insérer?
Ne pensez pas, les enregistrements sélectionnés seront insérés en une seule étape. Ainsi, les enregistrements de la table client ne seront pas visibles avant après l'insertion.
Ça a du sens. Je fais ça pour un projet scolaire et nous n'avons pas couvert PLSQL, donc je ne peux pas l'utiliser mais merci quand même! Je vais essayer de voir si je peux trouver un moyen de faire cela en SQL.
Essayez l'approche Edit dans ce cas.
Peut-être que le meilleur moyen est de créer une séquence commençant à 10000000:
INSERT INTO client (id, name, surname, surname2, dob, phone, email, address) SELECT NVL(cl_dni, SEQ_ID.nextval), cl_name, cl_surn1, cl_surn2, cl_birth, cl_phone, cl_email, cl_address FROM purchases WHERE NOT EXISTS(SELECT id from client WHERE client.id = purchases.cl_dni);
et de faire quelque chose comme ceci:
CREATE SEQUENCE SEQ_ID
START WITH 10000000
MAXVALUE 99999999
INCREMENT BY 1
NOCACHE
NOCYCLE;
Le Le problème de cette approche est que vous pourriez générer avec la séquence un cl_dni existant.
Le problème ne sont pas les nombres aléatoires, même si je remplace la partie de nombre aléatoire par une valeur prédéfinie qui n'existe pas dans la base de données d'origine, cela donne une erreur de violation de contrainte unique.
Quel moteur de base de données utilisez-vous pour cela? Définissez ces valeurs dans la base de données
AUTO INCREMENT, dans ce cas, vous n'aurez pas à insérer l'ID lorsque vous insérez un nouvel enregistrement car le moteur de base de données le gère pour vous. La violation de contrainte unique vous indique que cet identifiant existe déjà, ce qui signifie que vous avez donné à la colonne la contrainte unique. Pourquoi n'avez-vous pas créé la colonne (quelque chose comme a)INCREMENT AUTO CLÉ PRIMAIRE(le code exact dépend du moteur de base de données)?Vous ne comparez pas du tout à vos valeurs nouvellement calculées.
Je copie ces valeurs d'une autre table qui n'est pas la mienne. Chaque client a un identifiant aléatoire à 8 chiffres, je dois donc insérer les identifiants.
@ PM77-1 Je sais que je devrais idéalement comparer et je vais certainement l'implémenter. il n'y a qu'une seule ligne dans la base de données d'origine où le client n'a pas d'ID, donc à ce stade, je suppose simplement qu'il est peu probable que j'obtienne une valeur qui existe déjà dans la base de données. J'ai également essayé de coder en dur un nombre dont je sais qu'il n'existe pas dans la base de données et j'ai toujours la violation de contrainte unique, donc je pense que le problème principal est la partie WHERE NOT EXISTS. Je mettrai à jour la requête avec comparaison, merci!