Updating 2 tables when 1 table is updated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Updating 2 tables when 1 table is updated

Author

Topic

Sudhindra
Starting Member

13 Posts

Posted - 2009-02-12 : 00:54:27

Hi,
I Have a situation, I have a table with names lets call it NameMaster. The names in NameMaster will be used by another Table,table 2 say AddressTable where i store address with the Names from NameMaster. Now there can be names with a subtle change like jack & jack123.
. Now I modify Jack to Jack1. I need to update this change in my table 2 where I find Jack to Jack1. I need to do this using a stored Procedure.

Here are the 2 tables:
1:- NameMaster

NameID -- Name
1 -- Jack
2 -- Jack123
3 -- Mike

2) Address Table

AddID -- Name --Address
1 -- JAck -- XYZ,NY
2 -- Jack123 -- XYX, SC
3 -- Mike -- ZZZZ,CA
Now This is what I do, I modify Jack to Jack1 using Sp & this needs to be reflected in Address table too. Jack to JAck1, but JAck123 shouldnt be touched as first characters match.
Hpe I am clear. Any help will be really appreciated.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-12 : 00:56:35

Update uratable
set name = 'jack1'
where nameid = 1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-12 : 00:58:36

create proc updatejack
(
@id int,
@Name VARCHAR(32)
)
as
set nocount on
begin

Update NameTable
set name = @Name
where nameid = @id

Update Addresstable
set name = @Name
where addid = @id

end
set nocount off

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-12 : 00:59:56

try this

create procedure usp_test
(
@oldname varchar(64),
@newname varchar(64)
)
as
set nocount on
begin

update NameMaster
set name = @newname
where name = @oldname

update Address
set name = @newname
where name = @oldname

end
set nocount off

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-12 : 01:00:19

create proc usp_samp
(
@name varchar(32),
@id int
)
set nocount on

update namemaster
set name = @name
where nameid = @id

update addresstable
set name= @name
where addid = @id
set nocount off

Sudhindra
Starting Member

13 Posts

Posted - 2009-02-12 : 01:00:21

I am updating Jack to Jack in Namemaster table. Thats not the issue. The name Jack has to be update in the address table. There no NameID in Adress table, It has only Name column & address ID. So the Name Column in the Address table needs tobe updated. makign sure jack is replaced to jack1 & jack123 is not touched. ok theres a new requirment,
the Name Column in the address table will be like this
(Jack123)+(Myers)
(Jack)+(Sheldon)
So i need to change (Jack) to (Jack1) without touching (Sheldon). & If there is change of address there will be new record & the old record wont be modified So the address Table could have multiple records with (Jack)+(Sheldon) in the name column.
So all the records where I have (Jack) needs to be modified to (Jack1) without touching (Sheldon). or (Jack123).
Desperately need help..

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-12 : 01:02:53

my procedure will update name column of namemaster and address table at the same time

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-02-12 : 01:03:00

update namemaster
set name = @name
where name = @oldname

update addresstable
set name= @name
where name=@oldname

pass @oldname,@name as input parameters in procedure

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-12 : 01:11:32

quote:
Originally posted by Sudhindra

I am updating Jack to Jack in Namemaster table. Thats not the issue. The name Jack has to be update in the address table. There no NameID in Adress table, It has only Name column & address ID. So the Name Column in the Address table needs tobe updated. makign sure jack is replaced to jack1 & jack123 is not touched. ok theres a new requirment,
the Name Column in the address table will be like this
(Jack123)+(Myers)
(Jack)+(Sheldon)
So i need to change (Jack) to (Jack1) without touching (Sheldon). & If there is change of address there will be new record & the old record wont be modified So the address Table could have multiple records with (Jack)+(Sheldon) in the name column.
So all the records where I have (Jack) needs to be modified to (Jack1) without touching (Sheldon). or (Jack123).
Desperately need help..

use replace(colname,@oldname,@newname)
in update statement

Sudhindra
Starting Member

13 Posts

Posted - 2009-02-12 : 01:53:41

Thanks Warrior, I tried this
UPDATE Address
SET Name= REPLACE(Name,'(Jack)','(Jack1)') where Name like '%Jack%'

Thank you so much

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-12 : 02:16:27

welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 02:35:34

Let me get this straight...
You have an address table with usermaster information but no relationshhip to usermaster table?

How cool is that to maintain!

E 12°55'05.63"
N 56°04'39.26"

Sudhindra
Starting Member

13 Posts

Posted - 2009-02-12 : 04:07:43

very true its not cool at all, its a pain.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources