

\qecho **** Start by showing the problem table is empt _before_ the transaction starts
\qecho select * from site_membership;
select * from site_membership;


\qecho begin;
begin;


\qecho **** Move some other references out of the way
\qecho update sections set group_id = 207
\qecho where
\qecho                                           exists (select 1
\qecho                                                   from groups g
\qecho                                                   where
\qecho                                                   (g.principal_user_id = '144' or g.name = 'Press Office')
\qecho                                                   and
\qecho                                                   g.id <> '207'
\qecho                                                   and
\qecho                                                   group_id = g.id
\qecho                                                  )
\qecho ;

update sections set group_id = 207
where
                                          exists (select 1
                                                  from groups g
                                                  where
                                                  (g.principal_user_id = '144' or g.name = 'Press Office')
                                                  and
                                                  g.id <> '207'
                                                  and
                                                  group_id = g.id
                                                 )
;







\qecho **** Show what we will be trying to delete
\qecho select * from groups
\qecho where
\qecho                                          exists (select 1
\qecho                                                  from groups g
\qecho                                                  where
\qecho                                                  (g.principal_user_id = '144' or g.name = 'Press Office')
\qecho                                                  and
\qecho                                                  g.id <> '207'
\qecho                                                  and
\qecho                                                  groups.id = g.id
\qecho                                                 )
\qecho ;

select * from groups
where
                                          exists (select 1
                                                  from groups g
                                                  where
                                                  (g.principal_user_id = '144' or g.name = 'Press Office')
                                                  and
                                                  g.id <> '207'
                                                  and
                                                  groups.id = g.id
                                                 )
;




\qecho **** Attempt the delete ...
\qecho **** ...and watch the empty table from the start cause a ref. int. failure!
\qecho delete from groups
\qecho where
\qecho                                          exists (select 1
\qecho                                                  from groups g
\qecho                                                  where
\qecho                                                  (g.principal_user_id = '144' or g.name = 'Press Office')
\qecho                                                  and
\qecho                                                  g.id <> '207'
\qecho                                                  and
\qecho                                                  groups.id = g.id
\qecho                                                 )
\qecho ;

delete from groups
where
                                          exists (select 1
                                                  from groups g
                                                  where
                                                  (g.principal_user_id = '144' or g.name = 'Press Office')
                                                  and
                                                  g.id <> '207'
                                                  and
                                                  groups.id = g.id
                                                 )
;
