django 58 postgres alter author_id in album

all posts belongs to chuanshuo except second

album table, author id =  3 except second row

album table altered, every row author alternates

every post author alternates

--can't use row_number directly in update statement, have to create a new table first

create table if not exists album_rows(id, author_id, row_num) as
(select id, author_id, row_number() over (order by id) from music_album)

update album_rows
set author_id = (case when row_num % 2 = 0
then 3
else 4
end)

select * from album_rows

album_rows table, the last row id=26, row_num=25. id is not always in sequence, but row_num is

--replace album table author_id column with that from album_rows

update music_album a

set author_id = b.author_id

from album_rows b

where a.id = b.id

drop table album_rows

reference:
https://stackoverflow.com/questions/35142129/update-with-with-and-case-postgresql
http://www.postgresqltutorial.com/postgresql-row_number/
https://stackoverflow.com/questions/13473499/update-a-column-of-a-table-with-a-column-of-another-table-in-postgresql