INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

update table with one to many....quandry

update table with one to many....quandry

(OP)
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

RE: update table with one to many....quandry

(OP)
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

RE: update table with one to many....quandry

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

CODE

//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; 

RE: update table with one to many....quandry

(OP)
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

RE: update table with one to many....quandry

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close