Column Update from Another table 1

[mtepfer]

Have a column in Table A, want that column to be set to the value in another column that resides in Table B.

Example :

Table A.user_Field = Table B.Client_Num

They would be related on a primary/foreign key Table A.HTenant(FK) = Table B.hmyperson (PK)

What is the best way to go about this? A Trigger, set it on the column itself, or the table creation? Am using SQL 2005 and just not sure how this works.

Any info or information you could point me to is greatly appreciated. Thanks

Mike

 

[genomon]

Have a look in BOL at "INNER JOIN" and "UPDATE".

Always remember that you're unique. Just like everyone else.

 

[gmmastros]

It sounds like you are trying to de-normalize your tables. Unless your DB is used for datawarehousing, you are better off changing your data structure.

My suggestion would be to remove the column from one of your tables and then link to that table whenever you want the value from the column. This way, data is not duplicated (saving space) and your data will have better integrity because it is only stored in 1 place.

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheSQL]

Are you looking to do a one-time update, or an event-triggered update? Ex. are you just looking to run a script now and forget it, or set the value every time a record is inserted or updated?

 

[mtepfer]

TheSQL, This would be an event triggered update. Table A is a form table that has a form created at minimum once a year for each person(Table B), could be multiple forms in the year, but each time the form is created and the record entered into this table I want that field to look for the client number and insert it.

 

[TheSQL]

Well, you could either handle it in your INSERT logic, for the form record's INSERT statement, or use a SQL AFTER INSERT trigger. George also has a good point though - if there's any way to just reference Table B in Table A (ex. via a foreign key constraint) then you could just pull the data from both tables when you're retrieving it, and keep it normalized.

 

[mtepfer]

Thanks for the idea, just wrote a trigger and so far seems to be working fine.

USE [yardi_test]
GO
/****** Object: Trigger [dbo].[Insert_Client] Script Date: 11/06/2007 15:34:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[Insert_Client]
ON [dbo].[table a]
AFTER Insert,Update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
UPDATE A
SET A.User_Field = b.Client_Num
From TableA A left join TableB B on a.pk = b.fk

END

 

[TheSQL]

Great - gotta love triggers.