Post reply

SQL Update

  • April 14, 2008 at 3:31 pm

    #120956

    Sorry if the question has appeared before, but I can't seem to find it.

    I am trying to update names in my database (i.e. member.name) but there is a foreign key pointing to this value from another table (i.e. user.name).

    How can I make an update of a form

    UPDATE member

    SET name = 'John Smith'

    WHERE name = 'Joe Smith'

    without causing an error?

    I looked at triggers and procedures, but they don't seem to work for that purpose.

    Any help would be highly appreciated.

    Tom.

  • April 14, 2008 at 8:50 pm

    #802342

    You must update the other table, first... and both tables need to be updated.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 14, 2008 at 9:16 pm

    #802349

    If this is all I have to do, then I sure do feel silly. I think I have tried that already, but I will definitely give it another shot.

    I was thinking in the direction of dropping the constraint and then readding it but it seems like a lot of hustle.

    Thank you for your response.

  • April 14, 2008 at 10:58 pm

    #802356

    Jeff solution should work otherwise remove the relation between the two tables and then update both the tables. Reestablish the relation.

    This may work ...............

    🙂

  • April 16, 2008 at 6:22 am

    #803028

    When I need to do this, I usually use the "Generate Script" feature to generate a script that scripts all of the relationships between the 2 tables. Once the script is created and it looks OK, I drop the appropriate relationships between the 2 tables (i.e. the relationships that are preventing me from doing the update). After the update, I re-establish the relationships I dropped by executing the appropriate parts of the script that I created.

    Have a good day,

    Norene Malaney

  • April 17, 2008 at 12:37 am

    #803477

    [font="Verdana"]

    t.miecz (4/14/2008)

    Sorry if the question has appeared before, but I can't seem to find it.

    I am trying to update names in my database (i.e. member.name) but there is a foreign key pointing to this value from another table (i.e. user.name).

    How can I make an update of a form

    UPDATE member

    SET name = 'John Smith'

    WHERE name = 'Joe Smith'

    without causing an error?

    I looked at triggers and procedures, but they don't seem to work for that purpose.

    Any help would be highly appreciated.

    Tom.

    If you are updating child table, why don't you update parent table with Cascade Update option on?

    Mahesh[/font]

    MH-09-AM-8694

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply