使用存储过程更新 [英] update using stored procedure

问题描述

嗨 我想在sql2008中创建存储过程，以将数据从一个表更新到另一个表，对此


在此先感谢
pallavi

解决方案

可以用几种方法完成.一种方法是使用如先前文章所述的连接，另一种方法是使用相关的(或不相关的)子查询.一个简单的例子:

UPDATE MyTable
SET Field1 = (SELECT Something FROM AnotherTable WHERE MyTable.Field2 = AnotherTable.AnotherField)
WHERE ...

如果您可以编写需求或示例，那么解决方案可以针对您的特殊问题.

您好，请尝试一下，

TableA具有四列:a，b，c，d(a是主键列)
TableB具有五列:a1，b1，c1，d1，e1(a1和b1共同构成该表的主键)

两个表之间的外键关系基于A.a = B.a1

这两个表中的数据如下:
一，TableA
a b c d
1 x y z
2 a b c
3吨x z

二. TableB
a1 b1 c1 d1 e1
1 x1 y1 z1 40
2 a1 b1 c1 50

要求是编写一个SQL，以在满足连接条件且e1≥1的情况下，从TableB的b1，c1和d1列更新TableA的b，c和d列. TABLEB中的40.

SQL Server:

UPDATE TABLEA
SET b = TABLEB.b1，
c = TABLEB.c1，
d = TABLEB.d1
从TABLEA，TABLEB
TABLEA.a = TABLEB.a1
AND TABLEB.e1> 40
GO


更新后的结果:

a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z

试试这个
一个例子

create procedure sp_Updatedata  
(  
@OrderId int,  
@ShipName varchar(50),  
@ShipCountry varchar(50),  
@ShipMethodId int  
)  
  
as  
  
update Orders set ShipName=@ShipName,ShipCountry=@ShipCountry,ShipMethodId=@ShipMethodId where OrderId=@OrderId

hi i want create stored procedure in sql2008 to update data from one table to another table help for this


thanks in advance
pallavi

解决方案

This can be done in several ways. One way is to use join as described in earlier post, another could be to use correlated (or non-correlated) sub-queries. One simple example:

UPDATE MyTable
SET Field1 = (SELECT Something FROM AnotherTable WHERE MyTable.Field2 = AnotherTable.AnotherField)
WHERE ...

If you could write the requirements and perhaps an example, then the solutions could be targeted to your psecific problem.

Hi try this,

TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:
I. TableA
a b c d
1 x y z
2 a b c
3 t x z

II. TableB
a1 b1 c1 d1 e1
1 x1 y1 z1 40
2 a1 b1 c1 50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

SQL Server:

UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO


Results after the update:

a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z

Try this
Its An example

create procedure sp_Updatedata  
(  
@OrderId int,  
@ShipName varchar(50),  
@ShipCountry varchar(50),  
@ShipMethodId int  
)  
  
as  
  
update Orders set ShipName=@ShipName,ShipCountry=@ShipCountry,ShipMethodId=@ShipMethodId where OrderId=@OrderId

这篇关于使用存储过程更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！