TSql , Update master / child records
-
Hi.. I have the scene like this.... I have one master table say Users(userid, pwd) and child table customer(userid,name,address) both tables are related with field 'userid'. Now customer wants to update his userid. so userid will be updated in master as well as child record. could u suggest me the update query for this without deleting and inserting the records. thanks, Hemant
-
Hi.. I have the scene like this.... I have one master table say Users(userid, pwd) and child table customer(userid,name,address) both tables are related with field 'userid'. Now customer wants to update his userid. so userid will be updated in master as well as child record. could u suggest me the update query for this without deleting and inserting the records. thanks, Hemant
Your basic design is wrong, the userid should not be significant and should not be the primary key if it can be changed. However, if this is an existing system which you cannot change then simply do the update to both tables within a single transaction - assuming you do not have a foreign key constraint.
Bob Ashfield Consultants Ltd
-
Your basic design is wrong, the userid should not be significant and should not be the primary key if it can be changed. However, if this is an existing system which you cannot change then simply do the update to both tables within a single transaction - assuming you do not have a foreign key constraint.
Bob Ashfield Consultants Ltd
thanks for reply, i do have foreign key which is userid in customer table. will it be possible to write a single statement for update. could u guide., thanks
By: Hemant Thaker
-
Your basic design is wrong, the userid should not be significant and should not be the primary key if it can be changed. However, if this is an existing system which you cannot change then simply do the update to both tables within a single transaction - assuming you do not have a foreign key constraint.
Bob Ashfield Consultants Ltd
I think on update cascade will work here, what do u say ? thanks
By: Hemant Thaker
-
Hi.. I have the scene like this.... I have one master table say Users(userid, pwd) and child table customer(userid,name,address) both tables are related with field 'userid'. Now customer wants to update his userid. so userid will be updated in master as well as child record. could u suggest me the update query for this without deleting and inserting the records. thanks, Hemant
You should immediately remedy the primary key issue if you have control of the database. I would add an identity field UserNo to user table and the appropriate fields and keys to the FK table(s). As Bob said you have made a fundamental error in data design, fix it and you no longer have the update problem.
Never underestimate the power of human stupidity RAH