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

but I want to update Order using SQL to this:

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:

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.

CodePudding user response：

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;

CodePudding user response：

You can update from a calculated row_number.

update t 
set [Order] = rn
from (
  select BlogsId, PostsId, [Order]
  , rn = row_number() over (partition by PostsId order by BlogsId asc)
  from BlogPost
) t
where ([Order] is null or [Order]!=rn);

select *
from BlogPost
order by BlogsId, PostsId

Demo on db<>fiddle here