UPDATE Multiple tables on one filed. [modified]

[User 8047461]

Dear All, I have one table with column called testId. This Id is referenced in six other table columns. Its not like a primary /foreign key type but can be used to compare values using inner join. There is a requirement to changed all of this testId(10 digits code originally) to new set of TestId(11 digits code now). I could have done ON UPDATE CASCADE to change all testid's in other six tables if there was primary /foreign key defined. Is there any one who can give me a hint on the script to update on the master table so that all the other tables will contain the relevant changes on the testId. For example : Old value New Value testId = s401 I00010 s402 I00020 s403 I00030 s403 I00040 This is going be applied to over 30k rows hence i just needed a script to do it. Thank you so much for your time.

modified on Wednesday, July 13, 2011 5:37 PM

[Blue_Boy]

What did you do so far to archive your query? Can you show us your query?

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com

[Lost User]

It is always a good idea to have foreign key relationships on tables. Creating indexes on the foreign keys will also speed up queries. Why don't you try creating the foreign key relationships now with the ON UPDATE CASCADE option?

[smcnulty2000]

Since they aren't linked using a FK/PK relationship I'm not sure why you would have to change them in the same query. If s401 is always equal to I00010 then just write a mapping script and run it against all seven tables. If you are really worried then you could add a field where the updated values appear, leaving the old values in place, and then write another update that takes the new value, puts it in place of the old value and drop the updated value column. That way you can stop before the final step and check your work.

_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug...

[Niladri_Biswas]

Hi, I am providing a solution to this problem but ideally you should follow as what Shameel has said.. Let us create 4 tables (one master and three child tables) as under

CREATE TABLE [dbo].[tblmaster](
[testID] [varchar](50) NULL,
[Name] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblChild1](
[testID] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblChild2](
[testID] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[Address] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblChild3](
[testID] [varchar](50) NULL,
[Sex] [varchar](6) NULL,
[Address] [varchar](50) NULL,
[Phone Number] [varchar](50) NULL
) ON [PRIMARY]

insert into tblmaster
select 's401', 'name10' union all
select 's402', 'name11' union all
select 's403', 'name12' union all
select 's404', 'name14'

insert into tblChild1
select 's401', 'name20', 20 union all
select 's402', 'name21', 21 union all
select 's403', 'name22', 22 union all
select 's404', 'name23',23

insert into tblChild2
select 's401', 'name30', 'address30' union all
select 's402', 'name31', 'address31' union all
select 's403', 'name32', 'address32' union all
select 's404', 'name33', 'address33'

insert into tblChild3
select 's401', 'male', 'address40','9885446789' union all
select 's402', 'female', 'address41','12345678' union all
select 's403', 'male', 'address42','34567891' union all
select 's404', 'female', 'address43','0986234'

Select * from tblmaster
Select * from tblChild1
Select * from tblChild2
Select * from tblChild3

Now fire the below query

exec sp_msforeachtable N'
IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID(''?'') AND name = ''testID'')
EXEC (''UPDATE ? SET testID= case when testID = ''''s401'''' then ''''I00010''''
when testID = ''''s402'''' then ''''I00020''''
when testID = ''''s403'''' then ''''I00030''''
when testID = ''''s404'''' then ''''I00040''''
end'')'

N.B.~ Use this example as a reference for your problem. Thanks

Niladri Biswas

modified on Tuesday, July 19, 2011 5:48 AM