Proper way to create table friends in single statement
Don't know where shown_id came from, but I guess it's a typo.

Instead of populating only one column (user_id) of your friends table, you can add another user_id column to the select statement with friend_id alias. Immediate update query is not needed.

create table friends (
    user_id int,
    friend_id int,
    foreign key (user_id) references users (user_id),
    foreign key (friend_id) references users (user_id)
) as (
    select user_id, user_id as friend_id 
    from users
);