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
pwalter83
pwalter83
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2892 Visits: 2175
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?


Sorry Chris, you were right about this, could you please help me further ?

I wish to track the changes in table1 if the row is updated on table2 based on the condition that both tables are linked through primary-foreign key (VENDOR_ID). In other words, if VENDOR_NAME is changed on table2 then that change should be reflected on table1 through VENDOR_ID. Can this be achieved through a trigger ? If yes, then could you please guide me how can that be accomplished.

The DDL for table 1 and 2 are as below:

CREATE TABLE [dbo].[table1](
[SYSTEM_ID] [int] IDENTITY(1,1) NOT NULL,
[SYSTEM_NAME] [varchar](100) NOT NULL,
[VENDOR_ID] [varchar](100) NOT NULL


CREATE TABLE [dbo].[table2](
[VENDOR_ID] [int] IDENTITY(1,1) NOT NULL,
[VENDOR_NAME] [varchar](100) NOT NULL


Thanks in advance for your help.
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8856 Visits: 3718
There is no need for a trigger or something else. This is the key to a relational database design.
The value (of VENDOR_NAME) is stated on one position and is displayed on all locations with the use of key-references. With a SELECT statement you join the two tables together on the key reference (VENDOR_ID).
You can use the code below to display the joined data of both tables:
SELECT
SYSTEM_ID
, SYSTEM_NAME
, table1.VENDOR_ID
, VENDOR_NAME
FROM table1
INNER JOIN table2
ON table1.VENDOR_ID = table2.VENDOR_ID




You can update the VENDOR_NAME with statement:
UPDATE table2 SET VENDOR_NAME = 'new_name' WHERE VENDOR_NAME = '{existing name}'



If you execute the SELECT statement before and after the UPDATE, you can see the SELECT will always return the actual values.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64565 Visits: 17978
pwalter83 (8/16/2013)
I wish to track the changes in table1 if the row is updated on table2 based on the condition that both tables are linked through primary-foreign key (VENDOR_ID). In other words, if VENDOR_NAME is changed on table2 then that change should be reflected on table1 through VENDOR_ID. Can this be achieved through a trigger ? If yes, then could you please guide me how can that be accomplished.

The DDL for table 1 and 2 are as below:

CREATE TABLE [dbo].[table1](
[SYSTEM_ID] [int] IDENTITY(1,1) NOT NULL,
[SYSTEM_NAME] [varchar](100) NOT NULL,
[VENDOR_ID] [varchar](100) NOT NULL


CREATE TABLE [dbo].[table2](
[VENDOR_ID] [int] IDENTITY(1,1) NOT NULL,
[VENDOR_NAME] [varchar](100) NOT NULL


Thanks in advance for your help.


You are close here but not quite. What you want to do is have the VendorID in table1 NOT the VendorName. That way when the name of the vendor changes you don't have to do anything in any other table.

Using you ddl as a starting point, I changed Vendor_ID in table1 to be an int. It will hold the primary key of table2 instead of the value.

Here is a full example to demonstrate.


CREATE TABLE [dbo].[table1](
[SYSTEM_ID] [int] IDENTITY(1,1) NOT NULL,
[SYSTEM_NAME] [varchar](100) NOT NULL,
[VENDOR_ID] int NOT NULL
)

CREATE TABLE [dbo].[table2](
[VENDOR_ID] [int] IDENTITY(1,1) NOT NULL,
[VENDOR_NAME] [varchar](100) NOT NULL
)

insert table2
select 'My Vendor'

insert table1
select 'My System', SCOPE_IDENTITY()

select *
from table1 t
join table2 t2 on t.VENDOR_ID = t2.VENDOR_ID

update table2
set VENDOR_NAME = 'Name is changed'
where VENDOR_ID = 1

select *
from table1 t
join table2 t2 on t.VENDOR_ID = t2.VENDOR_ID



_______________________________________________________________

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)
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: 42729 Visits: 20015
pwalter83 (8/16/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?


Sorry Chris, you were right about this, could you please help me further ?

I wish to track the changes in table1 if the row is updated on table2 based on the condition that both tables are linked through primary-foreign key (VENDOR_ID). In other words, if VENDOR_NAME is changed on table2 then that change should be reflected on table1 through VENDOR_ID. Can this be achieved through a trigger ? If yes, then could you please guide me how can that be accomplished.

The DDL for table 1 and 2 are as below:

CREATE TABLE [dbo].[table1](
[SYSTEM_ID] [int] IDENTITY(1,1) NOT NULL,
[SYSTEM_NAME] [varchar](100) NOT NULL,
[VENDOR_ID] [varchar](100) NOT NULL


CREATE TABLE [dbo].[table2](
[VENDOR_ID] [int] IDENTITY(1,1) NOT NULL,
[VENDOR_NAME] [varchar](100) NOT NULL


Thanks in advance for your help.

Have you considered using views instead of tables as the data sources for your web pages? View1 and View2 could be based off of the same table but have different definitions.

“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.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2892 Visits: 2175
ChrisM@Work (8/16/2013)
pwalter83 (8/16/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?


Sorry Chris, you were right about this, could you please help me further ?

I wish to track the changes in table1 if the row is updated on table2 based on the condition that both tables are linked through primary-foreign key (VENDOR_ID). In other words, if VENDOR_NAME is changed on table2 then that change should be reflected on table1 through VENDOR_ID. Can this be achieved through a trigger ? If yes, then could you please guide me how can that be accomplished.

The DDL for table 1 and 2 are as below:

CREATE TABLE [dbo].[table1](
[SYSTEM_ID] [int] IDENTITY(1,1) NOT NULL,
[SYSTEM_NAME] [varchar](100) NOT NULL,
[VENDOR_ID] [varchar](100) NOT NULL


CREATE TABLE [dbo].[table2](
[VENDOR_ID] [int] IDENTITY(1,1) NOT NULL,
[VENDOR_NAME] [varchar](100) NOT NULL


Thanks in advance for your help.

Have you considered using views instead of tables as the data sources for your web pages? View1 and View2 could be based off of the same table but have different definitions.


No, I havent, but would it be possible to add data to a view ? The webpage allows the users to add new values and also change the existing values as well. Can that be made possible using views ?

Thanks.
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: 42729 Visits: 20015
Yes - have a read of this MS article.

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