You can use row_number and partition by site:
--Create a test table
drop table if exists public.test;
create table public.test (id serial, siteid varchar(10));
insert into public.test (siteid)
values ('L001'),('L002'),('L002'),('L001'), ('L001');
alter table public.test add column parcelid int; --Add the new column
"id" "siteid" "parcelid"
1 "L001"
2 "L002"
3 "L002"
4 "L001"
5 "L001"
--Calculate parcelid
update public.test a
set parcelid = sub.parcelid
from
(select id, row_number() over(partition by "siteid") parcelid from public.test) sub
where a.id=sub.id;
"id" "siteid" "parcelid"
1 "L001" 1
2 "L002" 1
3 "L002" 2
4 "L001" 2
5 "L001" 3
row_number()
isn't a GIS-centric task. Database Administrators would be a better place to research this.