?
Solved

Update 2 Tables at Once

Posted on 2012-08-24
9
Medium Priority
?
383 Views
Last Modified: 2012-08-31
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!
0
Comment
Question by:pposton
  • 4
  • 4
9 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 38331442
Create a trigger to update Activity when Client get updated.
0
 
LVL 16

Expert Comment

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

Author Comment

by:pposton
ID: 38345713
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 16

Expert Comment

by:DcpKing
ID: 38348499
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
 

Author Comment

by:pposton
ID: 38348580
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
 

Author Comment

by:pposton
ID: 38348597
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
 
LVL 16

Accepted Solution

by:
DcpKing earned 2000 total points
ID: 38348662
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
 

Author Closing Comment

by:pposton
ID: 38356529
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
 
LVL 16

Expert Comment

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

Mike
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Loops Section Overview
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question