根据同一表中的其他值标记/更新值(Flagging/updating values based on other values in the same table)

根据同一表中的其他值标记/更新值(Flagging/updating values based on other values in the same table)

免责声明 - 我对MySQL和数据库/ PHP的世界非常新。 但是，我已经被置于损坏控制的最后一刻，试图解决数据库问题。

背景：此站点具有上载功能，可以上载CSV（使用一个一致的模板），并将其插入名为“staging”的DB表中。 有很多专栏，但现在它只是一个表。 数据显示在数据表的前端，供用户/管理员查看。

这个人希望数据库使用MYSQL语句对某些数据进行QA / QC检查，我不确定该怎么做。 我正在考虑一个潜在的UPDATE语句，但无论哪种方式，我都需要在上传后在触发器中完成，这不会与实际上传冲突。

作为需要做的事情的一个例子：

其中一项检查是针对数据库中的两列：'lowSideMIUNumberDepart'和'lowSideMIUNumberArrive'。 有时，这两个字段中的数字匹配，有时则不匹配。 还有另一个名为'miu'的专栏依赖于这些专栏。 因此，如果数字匹配，'miu'应该说“NO”，如果它们不同，它应该说“YES”。

是和否已经在CSV中，但需要检查以确保它是正确的。 我需要找到一种检查方法，以便如果数字匹配但'miu'表示YES，它可以标记差异或将其更改为适当的值。

还有其他类似的问题，但它们都遵循这种模式，所以我希望如果我找到一套QA，我可以将相同的练习应用到其他7套。

任何帮助或想法让这个工作将非常感激。

Disclaimer - I'm very very new to the world of MySQL and databases/PHP. However, I've been thrown into a last minute position of damage control to try and get a database issue resolved.

Background: This site has an upload feature where a CSV can be uploaded (using one consistent template) and is inserted into a DB table called 'staging'. There are quite a few columns but for now it's just one table. The data is displayed on the front end in a datatable for the user/admin to view.

This person wants the database to preform QA/QC checks on some of the data with a MYSQL statement and I'm unsure how to do so. I'm thinking of a potential UPDATE statement but either way I would need this to be done in a trigger after upload that won't conflict with the actual upload.

As an example of what would need to be done:

One of the checks is against two columns in the database: 'lowSideMIUNumberDepart' and 'lowSideMIUNumberArrive'. Sometimes, the numbers in those two fields match and sometimes they don't. There's another column called 'miu'that depends on those columns. So, if the numbers match, 'miu' should say "NO" and if they are different it should say "YES".

Thes YES and NO are already in the CSV but it needs to check to make sure it's right. I need to find a way to check so that if the numbers match but 'miu' says YES, it can either flag the discrepency or change it to the appropriate value.

There are other issues like this, but they all follow this type of pattern so I'm hoping that if I figure it out for one set of QA I can apply the same practice to the other 7 sets.

Any help or ideas on getting this to work will be much appreciated.

最满意答案

看一下触发器文档：

触发器语法和示例

您可以构建一个触发器，只需将mui更新为insert上的正确值：

CREATE TRIGGER update_miu AFTER INSERT ON staging FOR EACH ROW SET miu = IF(NEW.lowSideMIUNumberArrive = NEW.lowSideMIUNumberDepart,"YES","NO");

Take a look at the triggers documenation:

Trigger Syntax and Examples

You could build a trigger that simply updates mui to the correct value on insert:

CREATE TRIGGER update_miu AFTER INSERT ON staging FOR EACH ROW SET miu = IF(NEW.lowSideMIUNumberArrive = NEW.lowSideMIUNumberDepart,"YES","NO");