CODEWITHSUNDEEP
sql-serversql-updatesql-insert
Sachith Wickramaarachchi
Sachith Wickramaarachchi

Reputation: 5862

How to increment row values by 1 when inserting

I have a table called LocationProduct and its structure is as follows,

+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1            | 100         | 1         |
+--------------+-------------+-----------+
| 1            | 101         | 2         |
+--------------+-------------+-----------+
| 1            | 102         | 3         |
+--------------+-------------+-----------+
| 1            | 103         | 4         |
+--------------+-------------+-----------+
| 1            | 104         | 5         |
+--------------+-------------+-----------+
| 1            | 105         | 6         |
+--------------+-------------+-----------+
| 2            | 100         | 1         |
+--------------+-------------+-----------+
| 2            | 101         | 2         |
+--------------+-------------+-----------+
| 2            | 102         | 3         |
+--------------+-------------+-----------+
| 2            | 103         | 4         |
+--------------+-------------+-----------+
| 2            | 104         | 5         |
+--------------+-------------+-----------+
| 2            | 105         | 6         |
+--------------+-------------+-----------+

Now I need to increment SortValue column's value by one when I insert a new row. As an example,

INSERT INTO BranchServices(FkBranchId,FkServiceId,SortValue)
VALUES(1,106,1)

I need to update FkLocationId = 1 related SortValue by one, I need the following output,

+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1            | 100         | 2         |
+--------------+-------------+-----------+
| 1            | 101         | 3         |
+--------------+-------------+-----------+
| 1            | 102         | 4         |
+--------------+-------------+-----------+
| 1            | 103         | 5         |
+--------------+-------------+-----------+
| 1            | 104         | 6         |
+--------------+-------------+-----------+
| 1            | 105         | 7         |
+--------------+-------------+-----------+
| 1            | 106         | 1         |
+--------------+-------------+-----------+

Here you can see I updated each old records SortValue by one. How can I do this?

Upvotes: 1

Views: 936

Answers (1)

sticky bit
sticky bit

Reputation: 37472

In one transaction first UPDATE the values then do the INSERT.

UPDATE branchservices
       SET sortvalue = sortvalue + 1
       WHERE sortvalue >= 1;

INSERT INTO branchservices
            (fkbranchid,
             fkserviceid,
             sortvalue)
            VALUES (1,
                    106,
                    1);

Upvotes: 2

Related Questions