This also needs testing to make sure that a locked account cannot be unlocked by a user doing their own password reset. Setting the deleted flag may be safer - if harder to reverse via the UI.
UPDATE pv_user SET locked = 1
FROM pv_user
WHERE
pv_user.id IN (
-- Patient
SELECT A.id
FROM
pv_user A
INNER JOIN pv_user_group_role B ON a.id = B.user_id
WHERE
B.role_id = 1
)
AND
pv_user.id NOT IN (
SELECT X.id
FROM
pv_user X
INNER JOIN pv_user_group_role Y ON X.id = Y.user_id
INNER JOIN pv_group Z ON Y.group_id = Z.id
WHERE
-- Patient
Y.role_id = 1 AND
-- Unit
Z.type_id = 1 AND
(
-- Scotland
Z.code LIKE 'S%'
OR
-- Edinburgh
Z.code IN ('EDINHF1', 'RSC02')
OR
-- Birmingham Paeds
Z.code = 'RQ3'
OR
-- Bristol Paeds
Z.code = 'RA723'
OR
-- Leeds Paeds
Z.code = '99RQR13'
OR
-- Liverpool Paeds
Z.code = 'RBS25'
-- OR
-- Evelina (RJ122) Not on PV
OR
-- GOSH
Z.code = 'RP4'
OR
-- Manchester Paeds
Z.code = 'RW3RM'
-- OR
-- Newcastle Paeds (RTDO2) Not on PV
OR
-- Nottingham Paeds
Z.code = '99RCSLB'
OR
-- Southampton Paeds
Z.code = '99RHM01'
OR
-- Ipswich
Z.code = 'RGQ02'
OR
-- MSE
Z.code IN ('RAJ', 'RAJ01', 'RQ8L0', 'RDDH0')
OR
-- Nottingham
Z.code IN ('RCSLB', 'RX1CC')
OR
-- Bristol
Z.code = 'REE01'
OR
-- Gloucester
z.code = 'RH641'
OR
-- Preston
Z.code = 'RMF01'
OR
-- IBD
Z.code = 'SALIBD'
OR
-- Diabetes
Z.code = 'DSF01'
)
AND
-- Exclude Paeds
age(now(), date_of_birth) >= '18 years'
)