Update table values if corresponding values change in another table

Question

Update table values if corresponding values change in another table

pwalter83

SSChampion

Points: 14575
More actions
July 24, 2013 at 5:03 am

#276235

Hi,
I am very new to SQL and really dont know how to phrase my question.
There are 2 tables linked through a primary key and if the values in one table change, the corresponding values in another table should be changed and reflected accordingly.
Does someone know what logic I need to apply for this to work ? Do I have to create a primary key-foreign key relationship and then create a trigger on the other table on which the values need to be updated ?
The values in the table will be changed through a webpage.
Any ideas would be appreciated.
Thanks.

Viewing 15 posts - 1 through 15 (of 25 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

pwalter83 (7/24/2013)
Hi,
I am very new to SQL and really dont know how to phrase my question.
There are 2 tables linked through a primary key and if the values in one table change, the corresponding values in another table should be changed and reflected accordingly.
Does someone know what logic I need to apply for this to work ? Do I have to create a primary key-foreign key relationship and then create a trigger on the other table on which the values need to be updated ?
The values in the table will be changed through a webpage.
Any ideas would be appreciated.
Thanks.

it sounds like you want to echo the same value in two places, is that correct? so if i change one table, it copies the same value to a related record in another?

if the above assumption is true, here's my thoughts:

This is breaking the whole normal forms rule: if a value is in one table, and there is a relationship between the two tables anyway, you can always join the tables together to get the value when it needs to be displayed in reference to the second table, so you want to try and avoid that.

so i'd say it's better to drop the column in the second table, and get the value in a query;

you could also create a view with that query above, to make it easier to access;

another possibility is to replace the column in table 2 with a calculated field, and the calculated field calls a user defined function that effectively gets the value you want from the other table, based on the join/relationship between the two.

because that UDF would be scalar, it would slow down access to the table if there is a ton of rows, which makes the query above idea more palatable in my eyes.

finally, if you are painted into a corner, and just have to do it, you could create a trigger that copies the value to the other table for insert and update.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

If you post the DDL (table definition) statements and include some sample data (INSERT statements) we can help you better.

You probably should use a single stored procedure to first update the values in the base-table. Include a second update statement in this stored procedure to update the values in the related table.

create procedure ({define all variables})

as

begin

update {base_table} set {column_name}={updated value}, ... where {id_column}={id_value}

update {related_table} set {column_name}={updated value}, ... where {foreign_key_column}={id_value}

end

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

pwalter83 SSChampion Points: 14575 More actions · Answer 3

Lowell (7/24/2013)
pwalter83 (7/24/2013)
Hi,
I am very new to SQL and really dont know how to phrase my question.
There are 2 tables linked through a primary key and if the values in one table change, the corresponding values in another table should be changed and reflected accordingly.
Does someone know what logic I need to apply for this to work ? Do I have to create a primary key-foreign key relationship and then create a trigger on the other table on which the values need to be updated ?
The values in the table will be changed through a webpage.
Any ideas would be appreciated.
Thanks.
it sounds like you want to echo the same value in two places, is that correct? so if i change one table, it copies the same value to a related record in another?
if the above assumption is true, here's my thoughts:
This is breaking the whole normal forms rule: if a value is in one table, and there is a relationship between the two tables anyway, you can always join the tables together to get the value when it needs to be displayed in reference to the second table, so you want to try and avoid that.
so i'd say it's better to drop the column in the second table, and get the value in a query;
you could also create a view with that query above, to make it easier to access;
another possibility is to replace the column in table 2 with a calculated field, and the calculated field calls a user defined function that effectively gets the value you want from the other table, based on the join/relationship between the two.
because that UDF would be scalar, it would slow down access to the table if there is a ton of rows, which makes the query above idea more palatable in my eyes.
finally, if you are painted into a corner, and just have to do it, you could create a trigger that copies the value to the other table for insert and update.

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Lowell has already asked but the question remains unanswered: why do you want to hold the same data in two places at once?

^{“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

Lowell SSC Guru Points: 323518 More actions · Answer 5

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?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pwalter83 SSChampion Points: 14575 More actions · Answer 6

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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?

^{“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

pwalter83 SSChampion Points: 14575 More actions · Answer 8

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.

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 9

You could put a trigger on the table but I don't think you can directly pass parameters to it, so there may not be a way to get the new values to your second table.

My suggestion would be to change your stored procedure to update both tables like someone else mentioned. This is a more direct approach and gives you total control of the transaction in one place.

Mark

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

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

pwalter83 SSChampion Points: 14575 More actions · Answer 11

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 SSC Guru Points: 186127 More actions · Answer 12

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

pwalter83 SSChampion Points: 14575 More actions · Answer 13

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 SSChampion Points: 14575 More actions · Answer 14

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.