How to update multiple rows in SQL Server?

Question

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

*Consumable* sample data and expected results, along with your attempt(s) will help us help you. — Thom A, Jun 01 '22 at 10:51
[Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) This is why I asked for *consumable* data. — Thom A, Jun 01 '22 at 11:00
I don't understand the logic. For example how does `29980.00` and `34650.00` become `41090.00` — Charlieface, Jun 01 '22 at 13:26
only 29980.00 change into 41090. First I select EmployeeId from EmployeePackages which needs update (5 rows will be updated). Let's take EmployeeId=1036 to update. Then I select which Grade (from GradePackages) will be selected for update purpose. Let's take GradeId=29 (5 rows). So new grade will be updated in EmployeePackages table. From the above, we can say that **Id** 16,17,18,19,20 (from GradePackages) will replace **Id** 13,14,15,16,17 (in EmployeePackages). Hope it clarifies. Thankyou. — M Waqar Anwar, Jun 01 '22 at 13:41

xQbert · Answer 1 · 2022-06-01T14:42:16.353

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

Thankyou. But it copies only the first row from the first table and pastes that first row into 5 rows into the 2nd table. What is needed is that 5 different rows copied from one table and pasted into another table. — M Waqar Anwar, Jun 01 '22 at 13:45
Odd... the join on EP and GP package Id should prevent that. I'll have to take a look. I would have thought the join and where clause would have prevented that issue. — xQbert, Jun 01 '22 at 14:29
@MWaqarAnwar I'm not able to recreate your issue: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=464f49ab07c92228bedf2d1f23055635 Special thanks to RF1991 as I stole their example and used my update on it. So I'd have to say it's in your implementation unless I don't understand the question. — xQbert, Jun 01 '22 at 14:33
I did have to make 1 update to also set the gradeID which I missed initially — xQbert, Jun 01 '22 at 14:42

RF1991 · Answer 2 · 2022-06-01T13:45:47.460

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

EmployeeId is Id from Employees Table. where as Id is of GradePackages Table. Similar is the case for EmployeePackages Table. — M Waqar Anwar, Jun 01 '22 at 12:17

How to update multiple rows in SQL Server?

2 Answers2