I've a table containing site_id and staff_id and is_valid columns . I would like to set is_valid as 'N' if site_id , staff_id combination is not unique. I used the following query to achieve this, but table is getting locked as I'm updating the same table from which I'm selecting the records. Please suggest a workaround for this.

UPDATE table1 a
   SET is_valid = 'N'
 WHERE NOT EXISTS (SELECT   1
                       FROM table1 b
                      WHERE b.site_id = a.site_id
                   GROUP BY site_id, staff_id
                     HAVING COUNT (1) = 1);

merge into table1 a
using (select site_id, staff_id
         from table1
        group by site_id, staff_id
       having count(*) > 1) b
on (a.site_id = b.site_id and a.staff_id = b.staff_id)
when matched then update set is_valid = 'N';