SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update table values if corresponding values change in another table


Update table values if corresponding values change in another table

Author
Message
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42095 Visits: 20008
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
Lowell has already asked but the question remains unanswered: why do you want to hold the same data in two places at once?


Hi,

The reason is - The same data needs to exist in two related tables is bacause this is part of the web project consisting of many sub-pages. If the value in one sub-page is changed then that saved value in the main webpage should be updated accordingly.

Each of these webpages are linked to individual tables so in fact the changes are to be made at the SQL level first.


Surely the main page and the subpage should be reading the same value from the same table?


Wish it was ? But no, as I mentioned before, each webpage is associated with a different table.

The main webpage is populated using the tables which are linked to the sub-pages and so the same columns exist in different tables. This implies that the tables should be normalised so that if any value is updated using the sub-pages, the change should be reflected in the main webpage.


This looks correct: "The main webpage is populated using the tables which are linked to the sub-pages". That's fine - a subpage and the main page can read the same data from the same table.
The rest of it is bonkers. It's the wrong way round.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
pwalter83
pwalter83
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2830 Visits: 2175
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
Lowell has already asked but the question remains unanswered: why do you want to hold the same data in two places at once?


Hi,

The reason is - The same data needs to exist in two related tables is bacause this is part of the web project consisting of many sub-pages. If the value in one sub-page is changed then that saved value in the main webpage should be updated accordingly.

Each of these webpages are linked to individual tables so in fact the changes are to be made at the SQL level first.


Surely the main page and the subpage should be reading the same value from the same table?


Wish it was ? But no, as I mentioned before, each webpage is associated with a different table.

The main webpage is populated using the tables which are linked to the sub-pages and so the same columns exist in different tables. This implies that the tables should be normalised so that if any value is updated using the sub-pages, the change should be reflected in the main webpage.


This looks correct: "The main webpage is populated using the tables which are linked to the sub-pages". That's fine - a subpage and the main page can read the same data from the same table.
The rest of it is bonkers. It's the wrong way round.


I am not sure where we are going with this debate. The webpages have already been setup and they are working properly. The only thing left to do is to normalise data so that the change on one table is reflected on the other table and hence updated accordingly on the main webpage.

Do you have any ideas or suggestions to accomplish this ?
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42095 Visits: 20008
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
Lowell has already asked but the question remains unanswered: why do you want to hold the same data in two places at once?


Hi,

The reason is - The same data needs to exist in two related tables is bacause this is part of the web project consisting of many sub-pages. If the value in one sub-page is changed then that saved value in the main webpage should be updated accordingly.

Each of these webpages are linked to individual tables so in fact the changes are to be made at the SQL level first.


Surely the main page and the subpage should be reading the same value from the same table?


Wish it was ? But no, as I mentioned before, each webpage is associated with a different table.

The main webpage is populated using the tables which are linked to the sub-pages and so the same columns exist in different tables. This implies that the tables should be normalised so that if any value is updated using the sub-pages, the change should be reflected in the main webpage.


This looks correct: "The main webpage is populated using the tables which are linked to the sub-pages". That's fine - a subpage and the main page can read the same data from the same table.
The rest of it is bonkers. It's the wrong way round.


I am not sure where we are going with this debate. The webpages have already been setup and they are working properly. The only thing left to do is to normalise data so that the change on one table is reflected on the other table and hence updated accordingly on the main webpage.

Do you have any ideas or suggestions to accomplish this ?


Sorry, no I don't. I can only reiterate this:

"The only thing left to do is to properly code the pages so that the change on one page is updated accordingly in the table and hence reflected on the other page."

- and wish you luck, because this is probably the thin end of a very big problem wedge.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
pwalter83
pwalter83
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2830 Visits: 2175
Lowell (7/24/2013)
pwalter83 (7/24/2013)

Thanks Lowell,

However, I was wondering is it not possible to implement this logic within the table structure because as I mentioned earlier, the values in one table would be updated through a webpage. The changed value then needs to be reflected in the other related table.

Would it be possible to achieve this by developing a primary key-foreign key relationship and then use trigger to update the values in the other table ?

Thanks.

Walter, yes it's possible using a trigger and a relationship between the two tables(ie a PK/FK), and i certainly understand that sometimes it's going to be done regardless of whether its following best practices and just doing the work to get the job done;
I just wanted to provide a decent discussion on all possible aspects i could think of;
if you have the DDL for the two tables, and the column(s) you want copied, we could help with the trigger;

but of course,here's my follow up question:

if you insert into Table1, do you know for sure rows exist in Table2, or do you have to create those rows int eh trigger as well? is this only for updting, so you know both rows already exist due to some other process having created them previously?


Thanks for your suggestion and sorry for the late reply.

This is only for updating the values when they change in the first table.

Is it possible to give an example on how to accomplish this or perhaps you can guide me into what to search for in google to achieve this scenario ?

Thanks again !
pwalter83
pwalter83
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2830 Visits: 2175
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
Lowell has already asked but the question remains unanswered: why do you want to hold the same data in two places at once?


Hi,

The reason is - The same data needs to exist in two related tables is bacause this is part of the web project consisting of many sub-pages. If the value in one sub-page is changed then that saved value in the main webpage should be updated accordingly.

Each of these webpages are linked to individual tables so in fact the changes are to be made at the SQL level first.


Surely the main page and the subpage should be reading the same value from the same table?


Wish it was ? But no, as I mentioned before, each webpage is associated with a different table.

The main webpage is populated using the tables which are linked to the sub-pages and so the same columns exist in different tables. This implies that the tables should be normalised so that if any value is updated using the sub-pages, the change should be reflected in the main webpage.


This looks correct: "The main webpage is populated using the tables which are linked to the sub-pages". That's fine - a subpage and the main page can read the same data from the same table.
The rest of it is bonkers. It's the wrong way round.


I am not sure where we are going with this debate. The webpages have already been setup and they are working properly. The only thing left to do is to normalise data so that the change on one table is reflected on the other table and hence updated accordingly on the main webpage.

Do you have any ideas or suggestions to accomplish this ?


Sorry, no I don't. I can only reiterate this:

"The only thing left to do is to properly code the pages so that the change on one page is updated accordingly in the table and hence reflected on the other page."

- and wish you luck, because this is probably the thin end of a very big problem wedge.


Sorry Chris, I know you have helped me before but this time I know that the solution lies in normalising the database and not changing the webpage coding.
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42095 Visits: 20008
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
Lowell has already asked but the question remains unanswered: why do you want to hold the same data in two places at once?


Hi,

The reason is - The same data needs to exist in two related tables is bacause this is part of the web project consisting of many sub-pages. If the value in one sub-page is changed then that saved value in the main webpage should be updated accordingly.

Each of these webpages are linked to individual tables so in fact the changes are to be made at the SQL level first.


Surely the main page and the subpage should be reading the same value from the same table?


Wish it was ? But no, as I mentioned before, each webpage is associated with a different table.

The main webpage is populated using the tables which are linked to the sub-pages and so the same columns exist in different tables. This implies that the tables should be normalised so that if any value is updated using the sub-pages, the change should be reflected in the main webpage.


This looks correct: "The main webpage is populated using the tables which are linked to the sub-pages". That's fine - a subpage and the main page can read the same data from the same table.
The rest of it is bonkers. It's the wrong way round.


I am not sure where we are going with this debate. The webpages have already been setup and they are working properly. The only thing left to do is to normalise data so that the change on one table is reflected on the other table and hence updated accordingly on the main webpage.

Do you have any ideas or suggestions to accomplish this ?


Sorry, no I don't. I can only reiterate this:

"The only thing left to do is to properly code the pages so that the change on one page is updated accordingly in the table and hence reflected on the other page."

- and wish you luck, because this is probably the thin end of a very big problem wedge.


Sorry Chris, I know you have helped me before but this time I know that the solution lies in normalising the database and not changing the webpage coding.



Heh that's okay Paul. I'd be interested to see how you get on with this - not in a "I told you so" kinda way, but curiosity. I can't see how it can cause anything but problems, and your confidence in the resolution tells me I've got the wrong end of the stick ;-)

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63544 Visits: 17966
Since you started a new thread and Chris pointed me back to this one I will post here.

I would agree that trying to keep this data in multiple tables is very bad design decision. It will cause you nothing but grief and anguish. You will need to implement triggers in each of the tables in order to even have a chance at making this work.

Lowell asked if you are certain that the rows always exist in all tables or if there is a possibility that you will have to create them. I never saw an answer from you on that.

Personally I would run away from this design as quickly as possible. Normalize your data and keep one and only copy of the details. There are a number of people on this thread already who can and will help you normalize your structures but you are going to have to provide some details. By details I don't mean another description of the issue, I mean actual ddl, sample data and a bit more detail about these web pages.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
LightVader
LightVader
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1249 Visits: 3038
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
Lowell has already asked but the question remains unanswered: why do you want to hold the same data in two places at once?


Hi,

The reason is - The same data needs to exist in two related tables is bacause this is part of the web project consisting of many sub-pages. If the value in one sub-page is changed then that saved value in the main webpage should be updated accordingly.

Each of these webpages are linked to individual tables so in fact the changes are to be made at the SQL level first.


Surely the main page and the subpage should be reading the same value from the same table?


Wish it was ? But no, as I mentioned before, each webpage is associated with a different table.

The main webpage is populated using the tables which are linked to the sub-pages and so the same columns exist in different tables. This implies that the tables should be normalised so that if any value is updated using the sub-pages, the change should be reflected in the main webpage.


This looks correct: "The main webpage is populated using the tables which are linked to the sub-pages". That's fine - a subpage and the main page can read the same data from the same table.
The rest of it is bonkers. It's the wrong way round.


I am not sure where we are going with this debate. The webpages have already been setup and they are working properly. The only thing left to do is to normalise data so that the change on one table is reflected on the other table and hence updated accordingly on the main webpage.

Do you have any ideas or suggestions to accomplish this ?


Sorry, no I don't. I can only reiterate this:

"The only thing left to do is to properly code the pages so that the change on one page is updated accordingly in the table and hence reflected on the other page."

- and wish you luck, because this is probably the thin end of a very big problem wedge.


Sorry Chris, I know you have helped me before but this time I know that the solution lies in normalising the database and not changing the webpage coding.



Using a trigger or stored procedure to update the same value in different tables is not the same as normalizing the database. Normalizing the database involves removing the duplicated data. ie. One table has all the status codes and other tables just link to it using a key.



The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
pwalter83
pwalter83
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2830 Visits: 2175
Sean Lange (7/29/2013)
Since you started a new thread and Chris pointed me back to this one I will post here.

I would agree that trying to keep this data in multiple tables is very bad design decision. It will cause you nothing but grief and anguish. You will need to implement triggers in each of the tables in order to even have a chance at making this work.

Lowell asked if you are certain that the rows always exist in all tables or if there is a possibility that you will have to create them. I never saw an answer from you on that.

Personally I would run away from this design as quickly as possible. Normalize your data and keep one and only copy of the details. There are a number of people on this thread already who can and will help you normalize your structures but you are going to have to provide some details. By details I don't mean another description of the issue, I mean actual ddl, sample data and a bit more detail about these web pages.


Thanks for your reply. Sorry I am late in replying as I was still trying to figure out a way to achieve it but in vain.

The webpage I am working on contains of a main webpage which is linked to a table (master table). The other webpages (subpages) are each linked to a separate sub-table.

The tables linked to the subpages have prepopulated data and sometimes the data needs to be amended or changed.

The main webpage has Add and Edit button to add or edit the data. When the Add button is clicked, it displays dropdownlists each linked to the sub-tables and same with the Edit button.

With your logic to normalize the table, the data linked to the main webpage should not be stored in the master table as actual values, is that what you mean ?

What if someone needs to see the actual data in SQL database in the main table ? They would only find ID columns that link to the other tables in stead of actual values/data. This is the part I am not clear about, can you please throw some light on it.

Thanks.
LightVader
LightVader
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1249 Visits: 3038
pwalter83 (7/31/2013)
Sean Lange (7/29/2013)
Since you started a new thread and Chris pointed me back to this one I will post here.

I would agree that trying to keep this data in multiple tables is very bad design decision. It will cause you nothing but grief and anguish. You will need to implement triggers in each of the tables in order to even have a chance at making this work.

Lowell asked if you are certain that the rows always exist in all tables or if there is a possibility that you will have to create them. I never saw an answer from you on that.

Personally I would run away from this design as quickly as possible. Normalize your data and keep one and only copy of the details. There are a number of people on this thread already who can and will help you normalize your structures but you are going to have to provide some details. By details I don't mean another description of the issue, I mean actual ddl, sample data and a bit more detail about these web pages.


Thanks for your reply. Sorry I am late in replying as I was still trying to figure out a way to achieve it but in vain.

The webpage I am working on contains of a main webpage which is linked to a table (master table). The other webpages (subpages) are each linked to a separate sub-table.

The tables linked to the subpages have prepopulated data and sometimes the data needs to be amended or changed.

The main webpage has Add and Edit button to add or edit the data. When the Add button is clicked, it displays dropdownlists each linked to the sub-tables and same with the Edit button.

With your logic to normalize the table, the data linked to the main webpage should not be stored in the master table as actual values, is that what you mean ?

What if someone needs to see the actual data in SQL database in the main table ? They would only find ID columns that link to the other tables in stead of actual values/data. This is the part I am not clear about, can you please throw some light on it.

Thanks.


Yeah, this is the way normalization works. To look at the actual values from the lookup tables with the other information in the main table, you would use a join. Something along the lines of

SELECT * FROM maintable AS a INNER JOIN subtable AS b ON a.subtableid = b.subtableid





The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search