update table with one to many....quandry

[m0nkey]

Ok....so i have been beating myself up and i have been crosseyed enough to put my head down and ask for help...it has defeated me and i need some guidance.
here goes:

i have two databases that i would like to update eachother when variables change from one to the other. right now i am going from lets say DB1 to DB2
the scenario is that i have a large number of accts and all are assigned to a single rep who is labeled as owner_id
owners change quite often in DB1 and i need DB2 to reflect that change.
DB2 is a phone dialer database that has this customer information. each owner (who is a sales rep) has a multitude of these lists.
a customer can be a part of multiple lists but a list can only belong to one owner.

here is a table in DB2 for:
LIST
-----
LIST_ID__list_type___file_number_______owner_id
10001____general_____abc123____________200
10002____follow______abc345____________200
10001____general_____abc345____________200
10003____general_____abc567____________300
10004____follow______abc890____________300

ok...so if i change the owner_id in DB1 i would like to update the owner_id in DB2. easy enough, but i also need to update the list_id. each list is owned by only one rep but a rep can own multiple lists. and a file can be a part of many lists but only one owner.
i need it to look like this if the owner of ABC345 changes from 200 to 300
LIST_ID__list_type_____file_number_______owner_id
10001____general_____abc123____________200
10002____follow______abc345____________300
10001____general_____abc345____________300
10003____general_____abc567____________300
10004____follow______abc890____________300

my question, and i applogize if this is so trivial, but how do i update the owner_id to have the correct list_id when the owners change and there are multiple lists? this seems so basic but its really kicking my a$$...
please guide me in the right direction...i can edit and add fields to either DB and do not have restrictions but i would like to maintain very little list management relationships if i could and not have to maintain a table manually evertime a list is created...

much thanks!!!
m

 

[m0nkey]

sorry, i meant the end result should be this....

LIST_ID__list_type_____file_number_______owner_id
10001____general_____abc123____________200
10004____follow______abc345____________300
10003____general_____abc345____________300
10003____general_____abc567____________300
10004____follow______abc890____________300

 

[jpadie]

a number of routes come to mind

1. why have separate tables for the same data? rewrite the app to use a common table or at least a common join table of owner_id to file_id
2. programmatically change both tables from whatever server software you are using.
3. create a trigger on the table that gets changed first in your programme

//NB not tested
DELIMITER |
CREATE
    TRIGGER db1Update
    AFTER UPDATE
    ON db1.tableName 
    FOR EACH ROW
      BEGIN
      UPDATE db2.tableName t2
      SET t2.owner_id = NEW.owner_id
      WHERE t2.file_number = NEW.file_number;
      END;
DELIMITER;

 

[m0nkey]

thank you so much for the response...
just to answer your questions, although i can play with the databases, i cant mess with the apps. that unfortunately is not an option...
but to expand on the trigger, that was the plan...and that query will work with one exception, i do not know how to update the DB for the correct list...my sloopy way was sort of like this:
i know that the owner_id for 10002 and 10001 is going to change from 200 to 300.
i also know that the list type for 10002 is general and the type for 10003 is follow.
also i know that the owner of list 10002 is 200 and the owner of list 10004 is 200
but what is going to happen is that abc345 is going to change owners and thus will change list_id's
but it is not just as easy as saying
set owner_id = 300
where file_number = ABC345

because i have two lists and i have to update new list_id for filenumber ABC345 to 10004 on account it is a type of follow and i have to set the new list_id for filenumber ABC345 with list_type of general to 10003.
i am not sure how to swing updating the list id to the appropriate list givent he different list type...i defintiely do not want both filenumbers of abc345 to have the same list...
i hope i am clear...its a bit hard for me to explain....thanks for the help...
m

 

[jpadie]

you will have to set out the business rules for the meanings of each of the table columns using some form of proper logical notation. I cannot derive the rules from your explanation.

but in general you can include any statement within a trigger. so it is legitimate to do selects and store the columns in variables, then reuse those variables in the update.