根据同一个表中的其他值标记/更新值

Question

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.

Answer 1

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");