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.
>Solution :
Physically maintaining an order or sequence of rows is rarely a good idea and can lead to data inconsistencies and other unforseen issues.
You would be better off creating a view that provides the additional Order column which you can do using row_number()
Create view BlogPosts as
select *,
Row_Number() over(partition by PostsId order by BlogsId) as [Order]
from blogpost;
If you really want to update an actual column in the table you could use a CTE
with b as (
select *,
Row_Number() over(partition by PostsId order by BlogsId) as seq
from blogpost
)
update b
set [Order] = seq;