Update multiple tables - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Update multiple tables

Author

Topic

mannahboy
Starting Member

11 Posts

Posted - 2005-07-24 : 19:46:19

I am trying to update multiple tables in one go. I have been able to insert data into mupltiple tables and thought the process would be similar.

I have inserted the SP below that i am using. It successfully updates the first table (called tbMembers) but doesnt update the 2nd (called tbMembersExtra)

It probabley has something to to with the where clause in the 2nd update. Ive set @RecordId to the @@IDENTITY.

Can someone please put me on the right track.

CREATE PROCEDURE diabey.spRenewPostTranInsert

(@systemMemId [int]=000000 output,
@EMAIL [varchar](250),
@USER1 [varchar](12), -- DOB
@USER2 [varchar](50), -- Password
@USER3 [varchar](50), -- FirstName
@USER4 [varchar](50), -- LastName
@USER5 [varchar](10)) -- MemberId

AS

DECLARE @PreTranId varchar(20)
DECLARE @MemberId varchar(50)
DECLARE @MemberPass varchar(50)
DECLARE @MemExpiry varchar(12)
DECLARE @Title varchar(4)
DECLARE @GivenName varchar(50)
DECLARE @LastName varchar(50)
DECLARE @DOB varchar(12)
DECLARE @Address varchar(250)
DECLARE @City varchar(50)
DECLARE @Postcode varchar(50)
DECLARE @State char(10)
DECLARE @Country varchar(200)
DECLARE @EEmail varchar(250)
DECLARE @HomePhone varchar(50)
DECLARE @WorkPhone varchar(50)
DECLARE @MobilePhone varchar(50)
DECLARE @PreferedPhone varchar(10)
DECLARE @MemCode varchar(3)
DECLARE @CardNumber varchar(50)
DECLARE @CardType varchar(15)
DECLARE @CardExpDate varchar(12)
DECLARE @TotalFee varchar(20)
DECLARE @Donation varchar(20)
DECLARE @tranDate varchar(12)
DECLARE @FamilyMembers int

DECLARE @Occupation varchar(150)
DECLARE @Diagnoses varchar(20)
DECLARE @Organisation varchar(150)
DECLARE @YearDiag varchar(12)
DECLARE @Survey varchar (20)
DECLARE @MemType varchar(50)
DECLARE @ConcessionCardNo varchar(20)
DECLARE @AccessLvl varchar(5)
DECLARE @Individual varchar(50)
DECLARE @OrgName varchar(50)
DECLARE @PrincipleOwner varchar(50)
DECLARE @BranchSupport varchar(10)
DECLARE @AnnualReturn varchar(10)

DECLARE @FamPreTranId varchar(20)
DECLARE @FamFirstName2 varchar(50)
DECLARE @FamLastName2 varchar(50)
DECLARE @FamDOB2 varchar(12)
DECLARE @FamConCard2 varchar(30)
DECLARE @FamDiag2 varchar(25)
DECLARE @FamYearDiag2 varchar(12)
DECLARE @FamNo2 varchar(5)

DECLARE @FamFirstName3 varchar(50)
DECLARE @FamLastName3 varchar(50)
DECLARE @FamDOB3 varchar(12)
DECLARE @FamConCard3 varchar(30)
DECLARE @FamDiag3 varchar(25)
DECLARE @FamYearDiag3 varchar(12)
DECLARE @FamNo3 varchar(5)

DECLARE @FamFirstName4 varchar(50)
DECLARE @FamLastName4 varchar(50)
DECLARE @FamDOB4 varchar(12)
DECLARE @FamConCard4 varchar(30)
DECLARE @FamDiag4 varchar(25)
DECLARE @FamYearDiag4 varchar(12)
DECLARE @FamNo4 varchar(5)

SELECT
@PreTranId = tbPreTransactions.PreTransactionID,
@Title = tbPreTransactions.Title,
@GivenName = tbPreTransactions.GivenName,
@LastName = tbPreTransactions.LastName,
@DOB = tbPreTransactions.DOB,
@Address = tbPreTransactions.Address,
@City = tbPreTransactions.City,
@Postcode = tbPreTransactions.Postcode,
@State = tbPreTransactions.state,
@Country = tbPreTransactions.Country,
@HomePhone = tbPreTransactions.HomePhone,
@WorkPhone = tbPreTransactions.WorkPhone,
@MobilePhone = tbPreTransactions.MobilePhone,
@PreferedPhone = tbPreTransactions.PreferedPhone,
@EEmail = tbPreTransactions.Email,
@CardNumber = tbPreTransactions.CardNumber,
@CardType = tbPreTransactions.CardType,
@CardExpDate = tbPreTransactions.expDate,
@TotalFee = tbPreTransactions.TotalFee,
@Donation = tbPreTransactions.Donation,
@tranDate = tbPreTransactions.trandate,
@FamilyMembers = tbPreTransactions.FamilyMembers,
@MemberPass = tbPreTransactions.MemberPass,
@MemExpiry = tbPreTransactions.MembershipExpDate,
@MemCode = tbPreTransactions.MembershipCode,

@Occupation = tbPreTransactionsExtra.Occupation,
@Diagnoses = tbPreTransactionsExtra.Diagnoses,
@Organisation = tbPreTransactionsExtra.Organisation,
@YearDiag = tbPreTransactionsExtra.YearDiag,
@Survey = tbPreTransactionsExtra.Survey,
@MemType = tbPreTransactionsExtra.MemType,
@ConcessionCardNo = tbPreTransactionsExtra.ConcessionCardNo,
@AccessLvl = tbPreTransactionsExtra.AccessLvl,
@Individual = tbPreTransactionsExtra.Individual,
@OrgName = tbPreTransactionsExtra.OrgName,
@PrincipleOwner = tbPreTransactionsExtra.PrincipleOwner,
@BranchSupport = tbPreTransactionsExtra.BranchSupport,
@AnnualReturn = tbPreTransactionsExtra.AnnualReturn,

@FamFirstName2 = tbPreTransactionsFamily.FirstName2,
@FamLastName2 = tbPreTransactionsFamily.LastName2,
@FamDOB2 = tbPreTransactionsFamily.DOB2,
@FamConCard2 = tbPreTransactionsFamily.ConcessionCard2,
@FamDiag2 = tbPreTransactionsFamily.Diagnoses2,
@FamYearDiag2 = tbPreTransactionsFamily.YearDiag2,
@FamNo2 = tbPreTransactionsFamily.FamilyNo2,

@FamFirstName3 = tbPreTransactionsFamily.FirstName3,
@FamLastName3 = tbPreTransactionsFamily.LastName3,
@FamDOB3 = tbPreTransactionsFamily.DOB3,
@FamConCard3 = tbPreTransactionsFamily.ConcessionCard3,
@FamDiag3 = tbPreTransactionsFamily.Diagnoses3,
@FamYearDiag3 = tbPreTransactionsFamily.YearDiag3,
@FamNo3 = tbPreTransactionsFamily.FamilyNo3,

@FamFirstName4 = tbPreTransactionsFamily.FirstName4,
@FamLastName4 = tbPreTransactionsFamily.LastName4,
@FamDOB4 = tbPreTransactionsFamily.DOB4,
@FamConCard4 = tbPreTransactionsFamily.ConcessionCard4,
@FamDiag4 = tbPreTransactionsFamily.Diagnoses4,
@FamYearDiag4 = tbPreTransactionsFamily.YearDiag4,
@FamNo4 = tbPreTransactionsFamily.FamilyNo4

FROM tbPreTransactions INNER JOIN
tbPreTransactionsExtra ON tbPreTransactions.PreTransactionID = tbPreTransactionsExtra.PreTransactionID LEFT OUTER JOIN
tbPreTransactionsFamily ON tbPreTransactions.PreTransactionID = tbPreTransactionsFamily.PreTransactionID
WHERE (tbPreTransactions.MemberPass = @USER2) AND (tbPreTransactions.GivenName = @USER3) AND (tbPreTransactions.LastName = @USER4) AND
(tbPreTransactions.Email = @EMAIL) AND (tbPreTransactions.MemberId = @USER5)

UPDATE tbMembers SET
MemberPass = @MemberPass,
FirstName = @GivenName,
LastName = @LastName,
DOB = @DOB,
StrAddress = @Address,
suburb = @City,
Postcode = @Postcode,
State = @State,
Country = @Country,
MailAddress = @Address,
email = @EEmail,
phone = @HomePhone,
ExpiryDate = @MemExpiry,
memberCode = @MemCode

WHERE tbMembers.MemberId = @USER5

DECLARE @RecordId int
SET @RecordID = @@IDENTITY

UPDATE tbMembersExtra SET
Title = @Title,
HomePhone = @HomePhone,
WorkPhone = @WorkPhone,
ConcessionCard = @ConcessionCardNo,
Occupation = @Occupation,
Organisation = @Organisation,
Diagnoses = @Diagnoses,
YearDiag = @YearDiag,
Survey = @Survey,
MemType = @MemType,
Individual = @Individual,
OrgName = @OrgName,
PrincipleOwner = @PrincipleOwner,
BranchSupport = @BranchSupport,
AnnualReturn = @AnnualReturn,
PreferedPhone = @PreferedPhone,
MobilePhone = @MobilePhone

WHERE tbMembersExtra.SystemMemId = @RecordID
GO

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-24 : 20:26:58

You don't get an @@IDENTITY when you update a record, only when you insert one.
You need to change the second statement so it also takes the @USER5 parameter, or have some intermediate statement that gives you the SystemMemID given a MemberID
Tim

mannahboy
Starting Member

11 Posts

Posted - 2005-07-24 : 20:34:45

Thanks for that.

What I do know is that both tables have a systemMemId that links them together. But the second update wont let me do a tbMembers.SystemMemId = tbMembersExtra.systemMemId

what kind of intermediate statement between the two updates can i use.

mannahboy
Starting Member

11 Posts

Posted - 2005-07-24 : 20:48:30

Perhaps if i could use this :
SELECT IDENT_CURRENT('tablename')

and store it in a variable name @RECORD_ID

then the where clause could read:
WHERE tbMembersExtra.SystemMemId = @RecordID

but @RECORD_ID = SELECT IDENT_CURRENT('tablename') produces syntaxt errors
any ideas?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-24 : 20:50:46

You can do a:

UPDATE tbMembersExtra SET
Title = @Title,
.....
WHERE tbMembersExtra.SystemMemId = (SELECT SystemMemID FROM tbMembers WHERE MemberId = @USER5)

mannahboy
Starting Member

11 Posts

Posted - 2005-07-24 : 21:03:29

your a bloody legend!!!!!! Thaks heaps mate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources