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
