How to update multiple rows in SQL Server?-CodePudding

Here is the Insert into select query which selects multiple rows from GradePackages table and inserts the data into EmployeePackages table:

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

Here @EmployeeId and GradeId have a single value each. But PackageId and Amount, each of them have 5 values.

The Insert query works fine, but the problem is with the update query. I need to update i.e. copy rows from the GradePackages table (where I input a specific GradeId) into the EmployeePackages table (where specific EmployeeId is input). I know it will work on a single row but there are multiple rows and that is the problem. I have tried different types of Update queries but it doesn't work. Have a look please. Thank you.

EmployeePackages table:

Id	EmployeeId	GradeId	PackageId	Amount
13	1036	30	1	29980.00
14	1036	30	2	5000.00
15	1036	30	3	0.00
16	1036	30	4	0.00
17	1036	30	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

GradePackages table:

Id	GradeId	PackageId	Amount
11	30	1	34650.00
12	30	2	5000.00
13	30	3	0.00
14	30	4	0.00
15	30	5	0.00
16	29	1	41090.00
17	29	2	6000.00
18	29	3	0.00
19	29	4	0.00
20	29	5	0.00

Output needed (EmployeePackages):

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

Expected results:

Let's say I select rows with GradeId = 29 (5 rows) from GradePackages and update EmployeePackages where EmployeeId = 1036

CodePudding user response：

I believe you said Update; not insert so...

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

Consider:

Example Fiddle: Special thanks to RF1991 so I didn't have to re-create the fiddle.

Resulting in:

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

CodePudding user response：

for update using 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

subquery fiddle

or with 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 fiddle