Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table users (id int, email text);
insert into users
VALUES
  (1, 'john@example.com'),
  (2, 'John@example.com'),
  (3, 'Peter@example.com'),
  (4, 'peter@EXAMPLE.com'),
  (5, 'arthur@EXAMPLE.com');
5 rows affected

create table posts (id int, userId int, content text);
insert into posts 
VALUES
  (1, 1, 'foo'),
  (2, 2, 'bar'),
  (3, 3, 'foo'),
  (4, 3, 'bar'),
  (5, 4, 'foo'),
  (6, 5, 'bar');
6 rows affected

update posts p
  set userid = u.user_id
from (
  select min(id) user_id, array_agg(id) as user_ids
  from users u
  group by lower(email)
  having count(*) > 1
) u
where p.userid = any(u.user_ids)
  and p.userid <> u.user_id;
2 rows affected

delete from users
where not exists (select * 
                  from posts p 
                  where users.id = p.userid);
2 rows affected

select *
from users;


id
email


1
john@example.com

3
Peter@example.com

5
arthur@EXAMPLE.com