asked on

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!

Sharath S

Create a trigger to update Activity when Client get updated.

DcpKing

Alternatively, look into using Transactions. Here's a simple example. It'll make both operations work together as if they were one.

pposton

ASKER

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

DcpKing

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

pposton

ASKER

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.

pposton

ASKER

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!!

ASKER CERTIFIED SOLUTION

DcpKing

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pposton

ASKER

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.

DcpKing

pposton: Was a pleasure to help! Come and try to answer questions - it's how we all learn!

Mike