J'ai passé de nombreuses heures à rechercher ce problème et à essayer diverses solutions, mais je ne trouve jamais vraiment de solution adaptée à mon problème spécifique. Je suis nouveau dans SQL et certains des exemples prêtent également à confusion.
Voici donc mon dilemme. J'ai une table d'équipement qui suit les changements d'huile pour des unités spécifiques dans une base de données. Le tableau ressemble à ceci:
**id UnitID Posted_On Date_Completed Note OverDueBy**
1 BT-109F 2019-02-04 2019-02-14 Hrs Overdue 23
1 BT-108G 2020-01-17 2020-01-22 Days Overdue 12
1 BT-122K 2020-01-02 2020-01-16 Days Overdue 12
1 BT-109F 2019-02-04 Days Overdue 3
Les exemples d'enregistrements ci-dessus doivent être créés ou mis à jour par la requête. La date de fin est entrée manuellement par le technicien quand il a terminé la vidange d'huile.
Ce que je veux que la requête fasse, c'est vérifier si une unité spécifique a un enregistrement où le champ 'Date_Completed' vide, et si c'est le cas, mettez à jour le champ 'OverDueBy' pour refléter la nouvelle valeur. Si tous les enregistrements de l'unité spécifiée ont les champs 'Date_Completed' remplis, alors la requête doit créer un nouvel enregistrement dont tous les champs seront remplis à l'exception du champ 'Date_Completed'.
Quelqu'un peut-il m'aider à construire une telle requête?
Merci Clan
3 Réponses :
Premièrement, j'utiliserais une vue plutôt qu'une table pour stocker les données calculées - cela réduit les frais de stockage et mettra à jour le calcul à chaque fois que la vue est ouverte.
Si vous utilisez SQLite, vous devriez être en mesure d'obtenir le retard en soustrayant le Posted_On de sa fonction pour renvoyer la date du jour quelque chose comme date ('now') ou julianday ('now') - lisez et testez les fonctions pour vous assurer qu'elles font ce que vous voulez.
Donc, dans le sens de: -
Create table CompletedUnits as select id, UnitID, max(posted_on) as latest_posted_on, '' as Date_Completed from ClansTable group by id, UnitID having count(*) = count(Date_Complete);
Si vous souhaitez stocker un instantané, vous pouvez toujours créer une table à partir d'une vue dans tous les cas: -
create table MyStoredOverduesOn4thFeb as select * from MyView;
J'apprécie votre aide et cela fonctionnera pour le terrain quand il est en retard par date, mais les unités peuvent également être en retard par la lecture du compteur horaire. La requête sera exécutée par une fonction de nœud rouge une fois par jour. Si le PMI (changement d'huile) n'est pas terminé, la requête va (je l'espère) mettre à jour la lecture actuelle du compteur horaire dans le champ.
Je suppose que je pourrais faire un filtrage de déclaration 'select' pour Unit et IsNull ('Date_Completed) et ensuite si j'obtiens une erreur, insérez sinon mettez à jour. (Je ne sais pas comment faire ça)
Ok, alors que diriez-vous d'utiliser les fonctions datetime () à la place? techonthenet.com/sqlite/functions/datetime.php
J'utilise des fonctions datetime dans la fonction node-red mais la requête est une autre histoire
Je pense que vous avez besoin de quelque chose comme ceci:
MERGE INTO EQUIPMENT A USING (SELECT * FROM EQUIPMENT B WHERE DATE_COMPLETED IS NULL) C ON (A.UNITID=C.UNITID) WHEN MATCHED THEN UPDATE SET A.OVERDUEBY="new value" WHEN NOT MATCHED THEN INSERT (A.id,A.UnitID,A.Posted_On,A.Date_Completed,A.Note,A.OverDueBy) VALUES (C.id,C.UnitID,C.Posted_On,NULL,C.Note,C.OverDueBy)
Je ne sais pas d'où proviendront les nouvelles valeurs de la mise à jour. Ce n'est pas clair dans votre question. Mais quelque chose comme ça pourrait fonctionner.
SQLite ne prend pas en charge MERGE.
Commencez par créer un index partiel unique pour la colonne UnitID:
INSERT INTO tablename(id, UnitID, Posted_On, Date_Completed, Note, OverDueBy) VALUES (?, 'BT-109F', ?, null, ?, ?) ON CONFLICT(UnitID) WHERE Date_Completed IS NULL DO UPDATE SET OverDueBy = ?;
afin qu'une seule ligne avec Date_Completed = null soit autorisée pour chaque UnitID .
Donc, une instruction comme celle-ci:
CREATE UNIQUE INDEX idx_unit ON tablename(UnitID) WHERE Date_Completed IS NULL;
n'insérera les nouvelles valeurs que s'il n'y a pas déjà de ligne pour UnitID = 'BT-109F' avec null dans Date_Completed .
Mais s'il existe une telle ligne, la colonne OverDueBy sera mise à jour.
Je ne sais pas quelles valeurs vous souhaitez insérer ou quelle sera la valeur mise à jour, alors remplacez le ? par les valeurs appropriées.
Merci. J'essaierai ça demain. (Je dois sortir pour le reste de l'après-midi). Je vous ferai savoir comment cela fonctionne. :)
J'ai créé l'index mais la deuxième partie ne fonctionne pas. J'obtiens le message suivant: La clause ON CONFLICT ne correspond à aucune contrainte PRIMARY KEY ou UNIQUE. Je ne sais pas quoi essayer ensuite.
Avez-vous copié le code tel quel avec `WHERE Date_Completed IS NULL`? La syntaxe est correcte. Vérifiez ceci: db-fiddle.com/f/2VNXYCUErV3ygFBgyKjSpJ/1
Je l'ai fait, mais je vais vérifier le lien et vous contacter. Encore une fois, nous devons sortir pour que je ne puisse pas vous répondre avant demain matin au plus tôt. (Membre de la famille à l'hôpital)
@forpasMerci beaucoup. Cela a parfaitement fonctionné. Je suis étonné que vous ayez trouvé cette solution. Il ne m’aurait pas été venu à l’esprit d’utiliser un index unique. Très intelligent. :)
Si c'est vraiment sqlite, je ne pense pas que vous le puissiez, ce dont vous avez besoin est plus une procédure stockée qu'une requête et sqlite ne prend pas en charge les procédures stockées. Ce que je ferais à votre place, j'écrirais 3 requêtes séparées et un morceau de python pour les exécuter selon la logique que vous avez mentionnée.
Utilisez peut-être NVL ( dba.stackexchange.com/a/16434 )
Si votre version
sqliteprend en charge ce que l'on appelle UPSERT, c'est le moyen d'aller stackoverflow.com/a/4330694/8339821 .Je suis sûr qu'UPSERT fonctionne avec mon sqlite et j'ai essayé de formater une requête en utilisant les exemples mais je n'ai pas assez d'expérience pour bien faire les choses. Personne?
Ce n'est pas à cela que sert upsert. Cela est déclenché par des violations de clé primaire ou d'index unique.
Personnellement, je calculerais simplement la valeur en retard à la volée (peut-être dans une vue ou si vous utilisez la dernière sqlite, une colonne générée) au lieu de la stocker et de la mettre à jour tous les jours.