Hi Firuz,
Check the below query.
You need to update the Id aswell as the parent having the same Id.
SQL
CREATE TABLE #collection
(
[id] INT PRIMARY KEY,
[type] VARCHAR(20),
[parent] INT
)
INSERT INTO #collection VALUES (0,'collection',Null);
INSERT INTO #collection VALUES (1,'collection',0);
INSERT INTO #collection VALUES (2,'collection',1);
INSERT INTO #collection VALUES (3,'product',4);
INSERT INTO #collection VALUES (4,'collection',0);
INSERT INTO #collection VALUES (5,'product',2);
INSERT INTO #collection VALUES (6,'product',4);
DECLARE @Id INT = 1
DECLARE @IdUpdate INT = 7
UPDATE #collection SET [id] = @IdUpdate WHERE [id] = @Id
UPDATE #collection SET [parent] = @IdUpdate WHERE [parent] = @Id
SELECT * FROM #collection
DROP TABLE #collection
Here Id 1 will be updated with 7 and the parent id 1 with 7.
