如何创建存储过程以使用外键关系更新两个表？ [英] How do I create a stored procedure to update two tables with a foreign key relationship?

问题描述

我有两个表Tenant和租约都有一个共同的字段租户id

租户（tnant-id，fname，lname，年龄）和租约（lase-id，deposit，entry-date） ，离开日期，租户ID）



i有一个asp.net系统，你必须使用已经存在的姓氏来更新。意思是你可以填写所有其他具有差异值的字段（您想要更新的值），但姓氏必须是您要更新其详细信息的租户。



什么我试过了：



更新租户设置fname = @ fname ....

其中fname = @ fname

更新租约设定押金= @存款

凡租户ID = @租户ID



它不工作

i have two tables Tenant and lease both have a common field tenant-id
Tenant(tnant-id,fname,lname,age) and lease(lase-id,deposit,entry-date,departure-date,tenant-id)

i have an asp.net sytem where you have to use a last name that already exists to update.meaning you can fill all the other fields with diff values(values you want to update) but the last name has to be of the tenant you want to update their details.

What I have tried:

update Tenants set fname=@fname....
where fname=@fname
update lease set deposit=@deposit
where tenant-id=@tenant-id

it is not working

推荐答案

嗯......看看你在做什么。您正在将租户名称更改为您开始使用的值！这意味着第一次更新 - 按照定义 - 从不进行任何更改，如果它确实很可能会弄乱你的数据库，因为姓氏甚至不是唯一的。你的专栏名称中的连字符也没有任何帮助...

你的意思可能是：

Well ... look at what you are doing. You are changing the Tenant name to the value that you are starting with! Which means that the first update - by definition - never makes any changes, and if it did would quite likely mess your DB up, as last names aren't even close to unique. And teh hyphens in your column names don't help at all either...
Probably what you meant to do was this:

UPDATE Tenants SET fname=@fname WHERE [tnant-id] = @tenantId
UPDATE lease SET deposit=@deposit WHERE [tenant-id]=@tenantId

但我建议您更改列名：

租户：

But I'd suggest that you change the column names:
Tenant:

ID, 
fName
lName
Age

租赁

Lease

ID
deposit
entryDate
departureDate
tenantID

使用起来更加一致和容易。

It's more consistent and easier to work with.

这篇关于如何创建存储过程以使用外键关系更新两个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！