我们可以在一个更新语句中更新多个表 [英] Can we update multiple table in one single update statement
问题描述
我有三张桌子......
1.Categorycrt
I have three tables......
1.Categorycrt
productid(primary key)
productcategory
Girthfrom
GirthTo
2。 Itemcre
2.Itemcre
Itemid(primary key)
Itemname
</pre lang="text">
3.Pricefix
<pre lang="vb">PriceId(primarykey)
Itemid(foreign key of Itemcre)
Productid(foreign Key of Categorycrt)
Rate</pre lang="text">
I need to update the Pricefix table...please help me with the query for updating the pricefix tabletable.....
I have a gridview for pricefix table
My aspx page
<pre lang="html"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
onrowcancelingedit="GridView1_RowCancelingEdit"
onrowdeleting="GridView1_RowDeleting"
onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating"
>
<columns>
<asp:TemplateField HeaderText="PriceId">
<itemtemplate>
<asp:Label ID="lblPriceId" runat="server" Text='<%#Eval("PriceId") %>'>'>
</itemtemplate>
<asp:TemplateField HeaderText="ItemName">
<itemtemplate>
<asp:Label ID="lblItemId" runat="server" Text='<%#Eval("Itemname") %>'>'>
</itemtemplate>
<edititemtemplate>
<asp:TextBox ID="txtItemId" runat="server" Text='<%#Eval("Itemname") %>'>' >
</edititemtemplate>
<asp:TemplateField HeaderText="ProductCategory">
<itemtemplate>
<asp:Label ID="lblPdtId" runat="server" Text='<%#Eval("ProductCategory") %>'>'>
</itemtemplate>
<edititemtemplate>
<asp:TextBox ID="txtPdtId" runat="server" Text='<%#Eval("ProductCategory") %>'>' >
</edititemtemplate>
<asp:TemplateField HeaderText="Rate">
<itemtemplate>
<asp:Label ID="lblRate" runat="server" Text='<%#Eval("Rate") %>'>'>
</itemtemplate>
<edititemtemplate>
<asp:TextBox ID="txtRate" runat="server" Text='<%#Eval("Rate") %>'>' >
</edititemtemplate>
<asp:TemplateField HeaderText="Action">
<itemtemplate>
<asp:LinkButton ID="edit" runat="server" CommandName="Edit" Text="Edit">
<asp:LinkButton ID="Delete" runat="server" CommandName="Delete" Text="Delete">
</itemtemplate>
<edititemtemplate>
<asp:LinkButton ID="Update" runat="server" CommandName="Update" Text="Update">
<asp:LinkButton ID="Cancel" runat="server" CommandName="Cancel" Text="cancel">
</edititemtemplate>
</columns>
点击网格更新的编辑按钮并取消链接按钮对于所选行显示.i需要更新pricefix table的productcategory,itemname和rate.actually in pricefix table中存储为productcategory和itemname ....的foreignkey值的值实际上在更新pricefix的gridview时更新的productcategory和itemname应该在它们的父表中更改,即Itemcre和Categorycrt和rate inPricefix表本身...
while clicking on the edit button of the grid update and cancel link button appears for the selected row .i need to update the productcategory,itemname and rate of the pricefix table.actually in pricefix table the value stored as foreignkey value of productcategory and, itemname....actually while updating the gridview of pricefix the updated productcategory and itemname should be changed in their parent table i.e, Itemcre and Categorycrt and rate inPricefix table itself...
推荐答案
http://stackoverflow.com/questions/2044467/how-to-update-two -tables-in-one-statement-in-sql-server-2005 [ ^ ]
http://social.msdn.microsoft .COM /论坛/ SQLSERVER / EN-US / eab6e48d-D22A-4e1f-ADD6-aaf3b47bdd84 /如何对更新多表式,SQLSERVER-2008-?论坛= TRANSACTSQL [<一个HREF =http://social.msdn.microsoft.com/Forums/sqlserver/en-US/eab6e48d-d22a-4e1f-add6-aaf3b47bdd84/how-to-update-multiple-tables-in-sql-server-2008 - ?forum = transactsqltarget =_ blanktitle =New Window> ^ ]
如果是外键关系,可以使用OnUpdate触发器更新依赖条目(SQL Server为此目的提供外键约束的属性 - OnUpdateCascade或类似的东西)。
在其他情况下,使用事务:它允许您捆绑许多SQL查询,然后将所有这些提交到数据库,或者在出现故障r的情况下ollback所有这些,从而保持数据库处于一致状态。
In case of a foreign key relationship, you can use an OnUpdate trigger to update the dependent entries (SQL Server offers a property on the foreign key constraint for that purpose - OnUpdateCascade or something like this).
In other cases, use a Transaction: it allows you to bundle many SQL queries, and then commit all of them to the db, or in case of failure rollback all of them, thus keeping the db in a consistent state.
这篇关于我们可以在一个更新语句中更新多个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!