如何在SQL Server中更新多行?

Question

下面是Insert into select查询，从GradePackages表中选择多行并将数据插入EmployeePackages表:

insert into EmployeePackages (EmployeeId, GradeId, PackageId, Amount) 
select 
@EmployeeId,
@GradeId,
PackageId,
Amount
from 
GradePackages   
where 
GradeId = @GradeId  

这里@EmployeeId和GradeId各有一个值。但是PackageId和Amount，每个都有5个值。

插入查询工作得很好，但问题在于更新查询。我需要更新，即从GradePackages表(我输入特定的GradeId)复制行到EmployeePackages表(其中特定的EmployeeId是输入)。我知道它将工作在单行，但有多行，这就是问题。我尝试过不同类型的更新查询，但它不起作用。请看一看。谢谢你。

EmployeePackagestable:

<表类>IdEmployeeIdGradeIdPackageId数量tbody><<tr>13103630129980.001410363025000.001510363030.001610363040.001710363050.0018103731日134000.0019103731日26000.0020103731日30.0021103731日40.0022103731日50.00

我相信你说的是Update;

DECLARE @GradeID AS Numeric(4,0)=29
DECLARE @EmployeeID as Numeric(4,0)=1036
UPDATE EmployeePackages 
SET EmployeePackages.Amount = GP.Amount,
EmployeePackages.GradeID = @GradeID  --added this and , above.
FROM EmployeePackages EP
INNER JOIN GradePackages GP
ON EP.PackageID = GP.PackageID
WHERE EP.EmployeeID = @EmployeeID
AND GP.GradeID = @GradeID

考虑:

• 参数化SQL UPDATE查询
基于ID匹配从一个表到另一个表的SQL更新

示例小提琴:特别感谢RF1991，所以我不必重新创建小提琴。

导致:

+---------+------------+---------+-----------+----------+
|   Id    | EmployeeId | GradeId | PackageId |  Amount  |
+---------+------------+---------+-----------+----------+
| 13      |       1036 |      29 |         1 | 41090.00 |
| 14      |       1036 |      29 |         2 |  6000.00 |
| 15      |       1036 |      29 |         3 |     0.00 |
| 16      |       1036 |      29 |         4 |     0.00 |
| 17      |       1036 |      29 |         5 |     0.00 |
| 18      |       1037 |      31 |         1 | 34000.00 |
| 19      |       1037 |      31 |         2 |  6000.00 |
| 20      |       1037 |      31 |         3 |     0.00 |
| 21      |       1037 |      31 |         4 |     0.00 |
| 22      |       1037 |      31 |         5 |     0.00 |
+---------+------------+---------+-----------+----------+

使用Subquery进行更新

UPDATE employeepackages
SET    employeepackages.gradeid = t.gradeid,
employeepackages.amount = t.amount
FROM   (SELECT GP.gradeid,
GP.packageid,
GP.amount
FROM   gradepackages GP
FULL JOIN employeepackages EP
ON EP.gradeid = GP.gradeid
AND EP.packageid = GP.packageid
WHERE  EP.gradeid IS NULL) t
WHERE  employeeid = @EmployeeId
AND employeepackages.gradeid = @GradeId
AND employeepackages.packageid = t.packageid  

子查询小提琴

或CTE

;with t as(
SELECT 
GP.gradeid ,
GP.packageid ,
GP.amount 
FROM   gradepackages GP
FULL JOIN employeepackages EP
ON EP.gradeid = GP.gradeid
AND EP.packageid = GP.packageid
WHERE  EP.gradeid IS NULL  )
UPDATE e
SET    e.gradeid = t.gradeid,
e.amount = t.amount
FROM   employeepackages e
JOIN t
ON e.packageid = t.packageid
WHERE  e.employeeid = @EmployeeId
AND e.GradeId = @GradeId  

cte小提琴