update能操作两个表吗。
我有两个表，一个总表一个明细。
每一种产品在这两个表中都有一条记录
我如何让一个表的一个字段等另一个表的一个字段
怎么写update语句
谢谢。
[解决办法]
用事物控制啊
[解决办法]
看吧
ALTER PROCEDURE sp_UpdatePolicyInfo
(
@HolderName nvarchar(255),
@HolderBirthday datetime,
@HolderDocumentType int,
@HolderDocument nvarchar(50),
@HolderEmail nvarchar(50),
@HolderGender int,
@HolderMarriage int,
@HolderIndustry int,
@HolderOccupation int,
@HolderAge nvarchar(10),
@HolderMobile nvarchar(50),
@HolderCompanyName nvarchar(255),
@HolderCompanyAddress nvarchar(255),
@HolderCompanyZip nvarchar(10),
@HolderCompanyPhone nvarchar(50),
@HolderFamilyAddress nvarchar(255),
@HolderFamilyZip nvarchar(10),
@HolderFamilyPhone nvarchar(50),
@HolderCollectionAddress nvarchar(255),
@HolderCollectionZip nvarchar(10),
@HolderCollectionPhone nvarchar(50),
@HolderContactAddress nvarchar(255),
@HolderContactZip nvarchar(10),
@HolderContactPhone nvarchar(50),
@HolderOperator int,
@HolderModifyTime datetime,
@HolderF_Relationship int, --投保人信息 27
@InsuredName nvarchar(255),
@InsuredBirthday datetime,
@InsuredDocumentType int ,
@InsuredDocument nvarchar(50),
@InsuredEmail nvarchar(50),
@InsuredGender int,
@InsuredMarriage int,
@InsuredIndustry int,
@InsuredOccupation int,
@InsuredAge nvarchar(10),
@InsuredMobile nvarchar(50),
@InsuredCompanyName nvarchar(255),
@InsuredCompanyAddress nvarchar(255),
@InsuredCompanyZip nvarchar(10),
@InsuredCompanyPhone nvarchar(50),
@InsuredFamilyAddress nvarchar(255),
@InsuredFamilyZip nvarchar(10),
@InsuredFamilyPhone nvarchar(50),
@InsuredCollectionAddress nvarchar(255),
@InsuredCollectionZip nvarchar(10),
@InsuredCollectionPhone nvarchar(50),
@InsuredContactAddress nvarchar(255),
@InsuredContactZip nvarchar(10),
@InsuredContactPhone nvarchar(50),
@InsuredOperator int,
@InsuredModifyTime datetime, --被保人信息 29
@BeneficiaryName nvarchar(255),
@BeneficiaryBirthday datetime,
@BeneficiaryDocumentType int,
@BeneficiaryDocument nvarchar(255),
@BeneficiaryGender int,
@BeneficiaryRelationship int,
@BeneficiaryOperator int,
@BeneficiaryModifyTime datetime, --手艺人信息 8
@PolicyKey int,
@PolicyPremiumKey decimal,
@PolicyAFYP decimal,
@PolicyInitalPaymentMode int ,
@PolicyRenewalPaymentMode int,
@PolicyProductKey int ,
@PolicyBankKey int,
@PolicyAccount nvarchar(50),
@PolicyCheckPerson int,
@PolicyOperator int,
@PolicyModifyTime datetime,
@PolicyDescription nvarchar(255) --保单信息
)
AS
BEGIN TRANSACTION
declare @HolderKey int ,@InsuredKey int ,@BeneficiaryKey int
declare @error1 int,@error2 int ,@error3 int,@error4 int
select @HolderKey=HolderKey,@InsuredKey=InsuredKey,@BeneficiaryKey=BeneficiaryKey from TR_P_Policy where PolicyKey=@PolicyKey
UPDATE TR_P_Holder
SET [Name] =@HolderName, Birthday =@HolderBirthday, DocumentType =@HolderDocumentType, Document =@HolderDocument, Email =@HolderEmail,
Gender =@HolderGender, Marriage =@HolderMarriage, Industry =@HolderIndustry, Occupation =@HolderOccupation, Age =@HolderAge, Mobile =@HolderMobile,
CompanyName =@HolderCompanyName,CompanyAddress =@HolderCompanyAddress, CompanyZip =@HolderCompanyZip, CompanyPhone =@HolderCompanyPhone,
FamilyAddress =@HolderFamilyAddress, FamilyZip =@HolderFamilyZip, FamilyPhone =@HolderFamilyPhone, CollectionAddress =@HolderCollectionAddress,
CollectionZip =@HolderCollectionZip, CollectionPhone =@HolderCollectionPhone, ContactAddress =@HolderContactAddress, ContactZip =@HolderContactZip,
ContactPhone =@HolderContactPhone, Operator =@HolderOperator, ModifyTime =@HolderModifyTime, F_Relationship =@HolderF_Relationship
where HolderKey=@HolderKey
set @error1=@@error
UPDATE TR_P_Insured
SET [Name] =@InsuredName, Birthday =@InsuredBirthday, DocumentType =@InsuredDocumentType, Document =@InsuredDocument, Email =@InsuredEmail,
Gender =@InsuredGender, Marriage =@InsuredMarriage, Industry =@InsuredIndustry, Occupation =@InsuredOccupation, Age =@InsuredAge, Mobile =@InsuredMobile,
CompanyName =@InsuredCompanyName, CompanyAddress =@InsuredCompanyAddress, CompanyZip =@InsuredCompanyZip, CompanyPhone =@InsuredCompanyPhone, FamilyAddress =@InsuredFamilyAddress,
FamilyZip =@InsuredFamilyZip, FamilyPhone =@InsuredFamilyPhone, CollectionAddress =@InsuredCollectionAddress, CollectionZip =@InsuredCollectionZip,
CollectionPhone =@InsuredCollectionPhone, ContactAddress =@InsuredCollectionAddress, ContactZip =@InsuredContactZip, ContactPhone =@InsuredContactPhone,
Operator =@InsuredOperator, ModifyTime =@InsuredModifyTime
where InsuredKey=@InsuredKey
set @error2=@@error
UPDATE TR_P_Beneficiary
SET [Name] =@BeneficiaryName, Birthday =@BeneficiaryBirthday, DocumentType =@BeneficiaryDocumentType, Document =@BeneficiaryDocument, Gender =@BeneficiaryGender,
Relationship =@BeneficiaryRelationship, Operator =@BeneficiaryOperator, ModifyTime =@BeneficiaryModifyTime
where BeneficiaryKey=@BeneficiaryKey
set @error3=@@error
UPDATE TR_P_Policy
SET PremiumKey =@PolicyPremiumKey, AFYP =@PolicyAFYP, InitalPaymentMode =@PolicyInitalPaymentMode, RenewalPaymentMode =@PolicyRenewalPaymentMode,
ProductKey =@PolicyProductKey, BankKey =@PolicyBankKey, Account =@PolicyAccount, CheckPerson =@PolicyCheckPerson, Operator =@PolicyOperator,
ModifyTime =@PolicyModifyTime, Description = @PolicyDescription
where PolicyKey=@PolicyKey
set @error4=@@error
IF (@error1 <> 0 and @error2 <> 0 and @error3 <> 0 and @error4 <> 0)
ROLLBACK
ELSE
COMMIT
[解决办法]
我同时更新了几个表！
[解决办法]
两个表有主外键撒
update a set a.name = b.name from a ,b where a.id = b.id
[解决办法]
帮助里有语法的
update table1
set table1.column = .....
from table1,table2,.....
where table1.column = table2.column ......(表之间的关联)
[解决办法]
写个带参数的存储过程嘛
create proc usp_up
(@xxx ...)
update a set a.name = @xxx,b.name=@xxx from a ,b where a.id = b.id
不知道LZ是不是这个意思
[解决办法]
觸發器
[解决办法]
麻烦 写两个update就行了 呵呵 省心 还不可能出错。。。。。。哈哈 万不得已才用得笨办法
[解决办法]
感觉还是用触发器比较好。
