如何在单个更新查询中更新多个表 [英] How Can I update multiple tables in a single update query

问题描述

我有三张桌子......



1.Categorycrt

 productid（主键）
 productcategory 
 Girthfrom 
 GirthTo 

2。 Itemcre

 Itemid（主键）
 Itemname 

3.Pricefix

 PriceId（primarykey）
 Itemid（Itemcre的外键）
 Productid（Categorycrt的外键）
 Rate 

我需要更新Pricefix表...请帮我查询更新pricefix tabletable .....



我有pricefix表格的gridview



我的aspx页面

< pre lang =HTML> < asp：GridView ID = GridView1 runat = server < span class =code-attribute> AutoGenerateColumns = False
onrowcancelingedit = GridView1_RowCancelingEdit
onrowdeleting = GridView1_RowDeleting
onrowediting = GridView1_RowEditing onrowupdating = GridView1_RowUpdating
>
< 列 < span class =code-keyword>>
< asp：TemplateField HeaderText = PriceId >
< < span class =code-leadattribute> itemtemplate >
< asp：标签 ID = lblPriceId runat = server 文字 =' <％ #Eval（ PriceId）％>' > '>
< / itemtemplate >

< asp：TemplateField HeaderText = ItemName >
< itemtemplate >
< asp：标签 ID = lblItemId runat = 服务器 文字 =' <％ #Eval（ < span class =code-string> Itemname）％>' > '>
< / itemtemplate >
< edititemtemplate >
< asp：TextBox ID = txtItemId runat = server 文本 =' <％ #Eval（ 项目名称）％>' > '>
< / edititemtemplate >

< asp：TemplateField HeaderText = ProductCategory >
< itemtemplate >
< asp：标签 ID = lblPdtId runat = server 文本 =' <％ #Eval（ ProductCategory）％>' > '>
< / itemtemplate >
< edititemtemplate >
< asp：TextBox ID = txtPdtId runat = server 文本 =' <％ #Eval（ ProductCategory）％>' > '>
< / edititemtemplate >

< asp：TemplateField HeaderText = Rate >
< itemtemplate >
< asp：标签 ID = lblRate runat = server 文字 =' <％ #Eval（ < span class =code-string> Rate）％>' > '>
< / itemtemplate >
< edititemtemplate >
< asp：TextBox ID = txtRate runat = server 文本 =' <％ #Eval（ 评价）％>' > '>
< / edititemtemplate >

< asp：TemplateField HeaderText = 行动 >
< itemtemplate >
< asp：LinkBut​​ton ID = 编辑 runat = server CommandName < span class =code-keyword> = 编辑 文本 = 编辑 >
< asp：LinkBut​​ton ID = 删除 runat = server CommandName = 删除 文本 = 删除 >
< / itemtemplate >
< edititemtemplate >
< asp：LinkBut​​ton ID = 更新 runat = server CommandName的 = 更新 文本 = 更新 >
< asp：LinkBut​​ton ID = 取消 runat = server CommandName = 取消 文本 = c ancel >
< / edititemtemplate >

< / columns >

while点击所选行的网格更新和取消链接按钮的编辑按钮。我需要更新pricefix表的productcategory，itemname和rate.actually在pricefix表中存储为productcategory和itemname的foreignkey值的值。 ...实际上在更新pricefix的gridview时，更新的productcategory和itemname应该在其父表中更改，即Itemcre和Categorycrt以及rate inPricefix表本身...

解决方案

< blockquote>我创建了一个示例存储过程，其中包含多个更新查询。

  CREATE   procedure  USP_TEST_Procedure 
  @ pTYPE   VARCHAR （ 10 ）= ' '，
  @ EMP_ID   VARCHAR （ 10 ）= ' < span class =code-string>'，
  @ EMP_NAME   VARCHAR  （ 10 ）= ' '，
  @ YMD   VARCHAR （ 20 ）= ' '，
 
  AS  
  BEGIN  
 
  DECLARE   @ TERM_ST_DT   DATETIME  = getdate（），
 声明  @ EMP_ROLE   varchar （ 20 ）; 
 如果 @ pTYPE = '  S1' 
  BEgin  
 选择 @ EMP_ROLE = max（role_Name）来自 user_Roles 其中 emp_id = @ EMP_ID 
  END  
  ELSE  
  BEGIN  
  SET  @ EMP_ROLE = '  Admin' 
  END  
 更新 table1  set  Role_name = @ EMP_ROLE 其中 emp_id = @ EMP_ID 
  update  table2  set  emp_name = @ EMP_NAME，joinDate = @ YMD 其中 emp_id = @ EMP_ID 
  insert   into  table3（empid，empname） values （  @ EMP_ID ， @ EMP_NAME ）
  END  
 
   -   执行程序 
 < span class =code-keyword> exec  USP_TEST_Procedure '  s1'，'  10001'，'  TESTUSER'，'  2014-05-08' 

您可以在存储过程中使用表类型变量。并在存储过程中传递全表..



你可以使用该变量做任何事情（插入/更新/操作）



点击以下链接获取更多信息



http://www.sqlservercentral.com/blogs/steve_jones/2012/09/19/creating-a-user-defined-table-type/ [ ^ ]

I have three tables......

1.Categorycrt

productid(primary key)
  productcategory
  Girthfrom
  GirthTo

2.Itemcre

Itemid(primary key)
Itemname

3.Pricefix

PriceId(primarykey)
Itemid(foreign key of Itemcre)
Productid(foreign Key of Categorycrt)
Rate

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

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

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...

解决方案

I have created a sample Stored procedure which has more then one update query.

CREATE procedure USP_TEST_Procedure
   @pTYPE           VARCHAR(10)  = '',                                                            
@EMP_ID         VARCHAR(10)   = '',                                                            
@EMP_NAME       VARCHAR(10)   = '',                                                            
@YMD             VARCHAR(20) = '',                                                    
                                                
AS                                                                
BEGIN                                                 
                                             
DECLARE @TERM_ST_DT  DATETIME = getdate(),    
Declare @EMP_ROLE varchar(20);
if @pTYPE='S1'
BEgin
select @EMP_ROLE=max(role_Name) from user_Roles where emp_id=@EMP_ID
END
ELSE
BEGIN
SET @EMP_ROLE='Admin'
END
Update table1 set Role_name=@EMP_ROLE where emp_id=@EMP_ID
update table2 set emp_name=@EMP_NAME,joinDate=@YMD where emp_id=@EMP_ID
insert into table3 (empid,empname) values(@EMP_ID,@EMP_NAME)
END 

-- To execute the procedure
exec USP_TEST_Procedure 's1','10001','TESTUSER','2014-05-08'

You can use table type variable in Stored procedures. and pass full table in stored procedure..

you can do any thing with that variable(insert/update/manipulation)

follow the below link for more information

http://www.sqlservercentral.com/blogs/steve_jones/2012/09/19/creating-a-user-defined-table-type/[^]

这篇关于如何在单个更新查询中更新多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！