SQL Update

Question

Post reply

SQL Update

t.miecz

Grasshopper

Points: 16
More actions
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.

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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)

t.miecz Grasshopper Points: 16 More actions · Answer 2

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.

Anipaul SSC-Insane Points: 24681 More actions · Answer 3

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

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

🙂

Norene Malaney SSCommitted Points: 1896 More actions · Answer 4

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

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 5

[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