Update 2 Tables at Once

I have a older asp program that I am rebuilding from scratch.  The SQL database was origionally built about 16 years ago as well.  What I need to do is to be able to update 2 tables at the same time. It's currently hosted on SQL2008r2.   Here is my scenario:

TABLE 1: Client.ss# + Client.DOB = Client.CCID
TABLE 2: Activity.CCID

When a user updates a clients SS# or the Date of Birth in the first table, it updates the CCID as well.  I need it to also update all of the activity records in the the second table where the old CCID was found to reflect the new CCID.

I am fairly new to this so I could use some help to accomplish this.  Examples would be appreciated.

Thanks in advance!
ppostonPresident/OwnerAsked:
Who is Participating?
 
DcpKingConnect With a Mentor Commented:
Your "if exists" is fine - you just don't need the "if not exists", because control will only get there if it doesn't exist! "if >1" won't work - you have to have something on each side of the operator!

As for the scope_identity: you're running an update operation, so there won't be one! Hence I commented it out :)

hth

Mike
0
 
SharathData EngineerCommented:
Create a trigger to update Activity when Client get updated.
0
 
DcpKingCommented:
Alternatively, look into using Transactions. Here's a simple example. It'll make both operations work together as if they were one.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ppostonPresident/OwnerAuthor Commented:
Well, I've been trying to figure this out by using a transaction.  I'm pasting my Sql statement below.  I get the error "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION", so I assume something is wrong in the query.  Here's the code below and thanks for the help.

ALTER PROC [dbo].[cliUpdt]

(
@id int output,
@CharityCkID varchar(15),
@CCID varchar (15),
@ss# varchar(11),
@dl# varchar(20),
@firstname varchar(50),
@lastname varchar(50),
@address varchar (50),
@city varchar (50),
@state varchar (20),
@zip varchar (10),
@phone varchar (50),
@contact varchar (50),
@contactphone varchar (50),
@date datetime,
@username varchar (15),
@DOB varchar(15),
@NumChildren varchar (5),
@NumAdults varchar (5),
@NumSeniors varchar (5),
@income varchar (9),
@gender varchar (3),
@maritalstatus varchar (3),
@ethnicity varchar (3),
@employmentstatus varchar (3),
@education varchar (10),
@spousename varchar (50)

)

AS

IF EXISTS
(SELECT 1

 FROM
	 dbo.client

 WHERE
	 CharityCkID = @CharityCkID)

BEGIN


PRINT 'CharityCkID Already Exists'
END

ELSE
       
                       
IF NOT EXISTS
(SELECT 1

 FROM
	 dbo.client

 WHERE
	 CharityCkID = @CharityCkID)
 
--THEN

BEGIN TRANSACTION

UPDATE client
SET
	CharityCkID = @CharityCkID, ss# = @ss#, dl# = @dl#, firstname = @firstname, lastname = @lastname, address = @address, city = @city, state = @state, zip = @zip, phone = @phone, contact = @contact, contactphone = @contactphone, date = @date, username = @username, DOB = @DOB, NumChildren = @NumChildren, NumAdults = @NumAdults, NumSeniors = @NumSeniors, income = @income, gender = @gender, maritalstatus = @maritalstatus, ethnicity = @ethnicity, employmentstatus = @employmentstatus, education = @education, spousename = @spousename

WHERE
	id = @id

SET @id = scope_identity()
UPDATE activity
SET
	CharityCKID = @CharityCkID, ss# = @ss#, dl# = @dl#
WHERE
	CharityCkID = @CCID

UPDATE family
SET
	CharityCkID = @CharityCkID
WHERE
	CharityCkID = @CCID

COMMIT TRANSACTION

Open in new window

0
 
DcpKingCommented:
Tighten up your code formatting and indent it properly and you'll notice a few things. Here's the code that should work for you:
ALTER PROC [dbo].[cliUpdt]
(
	@id int output,
	@CharityCkID varchar(15),
	@CCID varchar (15),
	@ss# varchar(11),
	@dl# varchar(20),
	@firstname varchar(50),
	@lastname varchar(50),
	@address varchar (50),
	@city varchar (50),
	@state varchar (20),
	@zip varchar (10),
	@phone varchar (50),
	@contact varchar (50),
	@contactphone varchar (50),
	@date datetime,
	@username varchar (15),
	@DOB varchar(15),
	@NumChildren varchar (5),
	@NumAdults varchar (5),
	@NumSeniors varchar (5),
	@income varchar (9),
	@gender varchar (3),
	@maritalstatus varchar (3),
	@ethnicity varchar (3),
	@employmentstatus varchar (3),
	@education varchar (10),
	@spousename varchar (50)
)
AS
Begin
	set nocount on		--
	IF EXISTS (	SELECT 1 
				FROM dbo.client
				WHERE	CharityCkID = @CharityCkID	)
	BEGIN
		PRINT 'CharityCkID Already Exists'
	END
	ELSE
	begin		--ALWAYS always always explicitly use begin and end!!!
--		IF NOT EXISTS(	SELECT 1				--this condition isn't needed, as it'll 
--						FROM dbo.client		--always be true, because
--						WHERE CharityCkID = @CharityCkID)
--		begin							--otherwise you'll be doing the printout!
			BEGIN TRANSACTION	--without the "begin" above, this is all that gets done!
				UPDATE client			--
					SET	CharityCkID = @CharityCkID, 
						ss# = @ss#, 
						dl# = @dl#, 
						firstname = @firstname, 
						lastname = @lastname, 
						address = @address, 
						city = @city, 
						state = @state, 
						zip = @zip, 
						phone = @phone, 
						contact = @contact, 
						contactphone = @contactphone, 
						date = @date, 
						username = @username, 
						DOB = @DOB, 
						NumChildren = @NumChildren, 
						NumAdults = @NumAdults, 
						NumSeniors = @NumSeniors, 
						income = @income, 
						gender = @gender, 
						maritalstatus = @maritalstatus, 
						ethnicity = @ethnicity, 
						employmentstatus = @employmentstatus, 
						education = @education, 
						spousename = @spousename
					WHERE id = @id
				SET @id = scope_identity()  --why this? You know @id already! 
				UPDATE activity
					SET	CharityCKID = @CharityCkID, 
						ss# = @ss#, 
						dl# = @dl#
					WHERE CharityCkID = @CCID
				UPDATE family
					SET	CharityCkID = @CharityCkID
					WHERE CharityCkID = @CCID
			COMMIT TRANSACTION
--		end		--matches the other begin above.
	end		--closes the ELSE
End                                            

Open in new window


When you check it out you'll see that you can take out the second condition and the associated "end", 'cos they're redundant.

hth

Mike
0
 
ppostonPresident/OwnerAuthor Commented:
Thanks...I'll give this a try and let you know asap.  

Once again , I'm real new to this stuff and this was the first transaction I've attempted.  

The reason for the scope identity and the "if...exists" portion was I am checking for an existing CharityCkID.  If it is found I wanted to stop the procedure and redirect to a page displaying the reason for the failed attempted (already exists).  If it doesn't exist then the procedure could go ahead.  I was grabbing the id from the scope identiy to use in the code behind to handle the distincition and  redirect.  

Thanks again...I'll check out the procedure.
0
 
ppostonPresident/OwnerAuthor Commented:
I think I need to change one thing in the code to get it working properly.  I grabbed some of this code from a client insert form, which would need to ensure the CharityCkId was unique.  However, this an update so it won't run as is because it sees the CharityCkId as in use.  I suppose instead of "If Exists" I need to check "If >1".  Would I simply replace it in that manner?

Thanks for all your help on this!!
0
 
ppostonPresident/OwnerAuthor Commented:
Well, I finally got it.  Just had to change the "If Exists" our for a statement to check if there was more than the one CharityCkID.

Thanks for everything and I'm sorry this this has taken me a while.
0
 
DcpKingCommented:
pposton: Was a pleasure to help!   Come and try to answer questions - it's how we all learn!

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.