Paul Edward Pagente Paul Edward Pagente - 1 month ago 19
MySQL Question

Multiple Criteria Update

Please bear with me with this one because i dont know how to explain this but still i will give my best. TY

I have 2 Tables and they are.

enter image description here

and I have 3 Criteria and they are

Reviewed
Partially Selected
Fully Selected


I have a program in VB.Net that Update the table
Items
everytime i select an item, to include that I always tag it and update
Added = yes
where I select an Item

For example I select
Peanut
then i the output would be like this.

enter image description here

Now if i continue selecting item then the column
added
will also update in table
Items


Now here is my question, did you see the
Trans No
? they are both columns of it in both tables. My question how can i update the table
PR
based on
added
column?

Here are the criteria

1.If all items with the same TransNo are selected then the status is
Fully Selected
in Table
PR


2.If not all items are selected but still there are
yes
in the
Added
column then the status in Table
PR
are Partially Selected

3.If there no items selected/No
yes
in
Added
table then the Status in PR are
Reviewed


4.How about if the item is
1(one)
only? How can i apply the 3 criteria

How can i make it?

TYSM for future help

Answer

I would use triggers on the items table to update the PR table. If the Added field can be set to yes after the record is inserted into the items table, then I would only use an after update and after delete triggers , otherwise, I would use an after insert as well.

I'll show one of the triggers, you can create the other ones based on this example. I would also consider moving the logic to a stored procedure that can be called by the triggers, given that the logic will be pretty much the same.

The code below counts the number of records in the items table with the same TransNo and counts the records for the same where Added is set to yes, and assigns these numbers to 2 variables (`select ... into ... statement).

Then in an if statement the status text is decided based on the logic you described.

Finally, the status field in the PR table is updated - assuming that this record already exists. You need to extend the code if this record in the PR table may not exist.

delimiter //
create trigger items_added_after_update after update on items
for each row
begin
    declare all_count int;
    declare yes_count int;
    declare status_text varchar(20);

    select count(TransNo), count(if(added='Yes',1,null)) into all_count, yes_count
    from items where TransNo=NEW.TransNo;
    if all_count=yes_count then set status_text='Fully Selected';
    elseif yes_count=0 then set status_text='Reviewed';
    else set status_text='Partially Selected';
    end if;

    update PR set PR.status=status_text where PR.TransNo=NEW.TransNo;
end //
delimiter ;