Paul Edward Pagente Paul Edward Pagente - 1 year ago 73
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

Partially Selected
Fully Selected

I have a program in VB.Net that Update the table
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
then i the output would be like this.

enter image description here

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

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
based on

Here are the criteria

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

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

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

4.How about if the item is
only? How can i apply the 3 criteria

How can i make it?

TYSM for future help

Answer Source

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
    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 ;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download