delete duplicates from tables contain join

Question

Hi i want to delete duplicates from a table like this:

I know to add row_number but I cant delete here is my code:

select *
  FROM building C JOIN (
 SELECT * 
from (
      select A.*,ROW_NUMBER()OVER(PARTITION BY T.YEAR_MONTH,T.LOCATION,T.OWNER ORDER BY 
      T.YEAR_MONTH,T.LOCATION,T.OWNER) AS RN
      from building a join(
                        select T.YEAR_MONTH,T.LOCATION,T.OWNER ORDER
                        from building  t
                        where t.sf <100 
                        group by T.YEAR_MONTH,T.LOCATION,T.OWNER
                        having count(*)>1 )t
     on T.YEAR_MONTH = a.YEAR_MONTH, T.LOCATION = a.LOCATION ,T.OWNER = a.OWNER) a
      )a
on T.YEAR_MONTH = a.YEAR_MONTH, T.LOCATION = a.LOCATION ,T.OWNER = a.OWNER
where a.rn= 1

I try to add delete in the beginning but I am getting an error massage : "commend NOT PROPERLY ENDED"

 DELETE C
  FROM building C JOIN (
  SELECT *
from (
      select A.*,ROW_NUMBER()OVER(PARTITION BY T.YEAR_MONTH,T.LOCATION,T.OWNER ORDER BY 
      T.YEAR_MONTH,T.LOCATION,T.OWNER) AS RN
      from building a join(
                        select T.YEAR_MONTH,T.LOCATION,T.OWNER ORDER
                        from building  t
                        where t.sf <100 
                        group by T.YEAR_MONTH,T.LOCATION,T.OWNER
                        having count(*)>1 )t
     on T.YEAR_MONTH = a.YEAR_MONTH, T.LOCATION = a.LOCATION ,T.OWNER = a.OWNER) a
      )a
on T.YEAR_MONTH = a.YEAR_MONTH, T.LOCATION = a.LOCATION ,T.OWNER = a.OWNER
where a.rn= 1

Answer 1

I am not sure if I understood your question. but you can just try something like this:

DELETE FROM building 
WHERE building.id in (SELECT building.id FROM ...{your query } )