Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2009
    Posts
    9

    Question Unanswered: how to duplicate a row where tables are linked

    I am using mssql but I think this is generic.
    how to duplicate a row where the table could have dependencies?
    ie. table B has a foreign key into tables A, B and C. table A is not a dependent table.
    thanks,
    Anil

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which row do you want to duplicate?

    and in which table is this row?

    and does this table have a primary key?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by anilp
    I am using mssql but I think this is generic.
    how to duplicate a row where the table could have dependencies?
    ie. table B has a foreign key into tables A, B and C. table A is not a dependent table.
    thanks,
    Anil
    Your table ought to have a key which would prevent duplicate rows. I suggest you rethink or restate your requirement to be clear about what key(s) are involved and exactly what you want to duplicate.

  4. #4
    Join Date
    Sep 2009
    Posts
    9
    Quote Originally Posted by r937
    which row do you want to duplicate?

    and in which table is this row?

    and does this table have a primary key?
    Sorry for not specifying more clearly.
    the row is in table B. All the tables have primary keys.
    by 'duplicate', I mean cloning the table row and inserting the clone in the table with another primary key, of course.
    Someone proposed a solution but that involved add ing a 'parent-id' column to each table. A solution seems difficult because the foreign keys are not null. So if there are 15 tables, I dont know how.

  5. #5
    Join Date
    Sep 2009
    Posts
    9
    Quote Originally Posted by dportas
    Your table ought to have a key which would prevent duplicate rows. I suggest you rethink or restate your requirement to be clear about what key(s) are involved and exactly what you want to duplicate.
    Sorry for not specifying more clearly. Please see above.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you explain your real world scenario rather than "A" and "B"? I find it rather confusing to follow to be honest!
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anilp
    by 'duplicate', I mean cloning the table row and inserting the clone in the table with another primary key, of course.
    of course


    Quote Originally Posted by anilp
    Someone proposed a solution but that involved add ing a 'parent-id' column to each table.
    that someone is misguided, to say the least

    alternatively, it's possible that this someone knew more about your tables than we do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2009
    Posts
    9
    Quote Originally Posted by gvee
    Can you explain your real world scenario rather than "A" and "B"? I find it rather confusing to follow to be honest!
    Suppose a natural disaster (flood, tornado, fires...) strikes, and farmer Brown's crops, fences, soil, buildings are damaged. So he files an application requesting financial aid to help him repair the damage. He lists the project line items or techniques (building fence, reseeding crops etc.) and gets approval for each and a percentage of his request is paid for.

    So looking at a subset:

    main tables:
    1) aid_request
    primary key: aid_request_id

    2) farmer
    primary key: farmer_id


    linking tables:

    3) project_item
    primary key: project_item_id
    foreign keys (not null): aid_request_id

    4) farmer_project_item_estimate
    primary key: farmer_project_item_estimate_id
    foreign keys (not null): farmer_id, project_item_id, aid_request_id

    5) farmer_aid_request
    primary key: farmer_aid_request_id
    foreign keys (not null): aid_request_id, farmer_id

    Suppose the aid application is to be versioned, then the new version of the application is created by cloning the initial application, so a copy of the row(s) in the original table is created and inserted there.

    We can create an ordering of the tables to be processed. We start with the independent tables first.
    To clone the row(s) related to the aid request in farmer_project_item_estimate, how would we do so?
    We would create the three tables it depends upon, and then use the ids farmer_id, project_item_id, aid_request_id to insert a row in this table, since the foreign keys are not null.
    Is there a general way or an algorithm to do this? would I have to create a dependency graph - but what if there is a 1:N relationship (so multiple rows are associated as in project_item). I am wondering how one can do it in SQL, or SQL and Java?
    Last edited by anilp; 09-23-09 at 17:22.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this isn't duplicating a row, this is duplicating a comprehensive set of related rows

    you'll have to write some application logic to do that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •