Following is the query to update sql table with joins
UPDATE a
SET a.sortorder = b.sort_order
FROM picture AS a
INNER JOIN (select row_number() over (order by id) as sort_order,id,projectid from picture
where projectid = 61
) AS b
ON a.id = b.id
where a.projectid = 61
select * from picture order by id desc
