I wonder how to update order in this table for many-to-many relationship using SQL based on PostsId. So my table now looks like:
I’m using SQL Server
| BlogsId | PostsId | Order | |
|---|---|---|---|
| 1 | 1 | 1 | 0 |
| 2 | 2 | 1 | 0 |
| 3 | 3 | 2 | 0 |
| 3 | 4 | 2 | 0 |
| 3 | 5 | 3 | 0 |
| 3 | 6 | 3 | 0 |
but I want to update Order using SQL to this:
| BlogsId | PostsId | Order | |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 2 | 1 | 2 |
| 3 | 3 | 2 | 1 |
| 3 | 4 | 2 | 2 |
| 3 | 5 | 3 | 1 |
| 3 | 6 | 3 | 2 |
So for example: Blog with Id 3 is the first blog in Post with Id 2, Blog with Id 4 is the second Blog in Post with Id 2 and etc…
I’ve tried this:
DECLARE @myVar int
SET @myVar = 0
UPDATE [dbo].[BlogPost]
SET @myVar = [Order] = @myVar + 1
but then I got this:
| BlogsId | PostsId | Order | |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 2 | 1 | 2 |
| 3 | 3 | 2 | 3 |
| 3 | 4 | 2 | 4 |
| 3 | 5 | 3 | 5 |
| 3 | 6 | 3 | 6 |
So, I think I should do something in WHERE part (with Distinct maybe) but I don’t know exactly what. I could write something in C# to do what I want but I don’t know how to write something like this in SQL.